Thread: 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';
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=#
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=#
Bill Nedell wrote:
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/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN-QUERIES
Try EXECUTE 'SELECT ....
DECLARE
tl pg_tables%ROWTYPE;
BEGIN
FOR tl IN SELECT * FROM pg_tables LOOP
RAISE INFO ''table %'', tl.tablename;
SELECT * FROM tl.tablename;
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/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN-QUERIES