471,354 Members | 2,085 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

selecting table at execution with PL/PgSQL

I am trying to write a PL/PgSQL function that can be given a table name as an argument at execution time and I don't see any way of doing it. I want the user to be able to specify the table dynamically when the function is run.

As an example, if I try the following:

CREATE OR REPLACE FUNCTION scrub() RETURNS VARCHAR AS '
DECLARE
tl pg_tables%ROWTYPE;
BEGIN
FOR tl IN SELECT * FROM pg_tables LOOP
RAISE INFO ''table %'', tl.tablename;
SELECT * FROM tl.tablename;
END LOOP;
return ''SUCCESS'';
END;
' LANGUAGE 'plpgsql';

I get the following error:

testb=# select scrub();
INFO: table pg_conversion
WARNING: Error occurred while executing PL/pgSQL function scrub
WARNING: line 8 at SQL statement
ERROR: parser: parse error at or near "$1" at character 17
testb=#

Nov 12 '05 #1
1 1988
Bill Nedell wrote:
I am trying to write a PL/PgSQL function that can be given a table
name as an argument at execution time and I don't see any way of doing
it. I want the user to be able to specify the table dynamically when
the function is run.

As an example, if I try the following:

CREATE OR REPLACE FUNCTION scrub() RETURNS VARCHAR AS '
DECLARE
tl pg_tables%ROWTYPE;
BEGIN
FOR tl IN SELECT * FROM pg_tables LOOP
RAISE INFO ''table %'', tl.tablename;
SELECT * FROM tl.tablename;


Try EXECUTE 'SELECT ....

where the execute parameter is created by assembling the select
statement string.

It's all in the documentation.

http://www.postgresql.org/docs/7.3/s...NG-DYN-QUERIES

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Agoston Bejo | last post: by
17 posts views Thread by Dr NoName | last post: by
3 posts views Thread by Alex Satrapa | last post: by
4 posts views Thread by Scot L. Harris | last post: by
1 post views Thread by Graeme Hinchliffe | last post: by
5 posts views Thread by Miquel van Smoorenburg | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.