By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

selecting table at execution with PL/PgSQL

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.