selecting table at execution with PL/PgSQL

From: "Bill Nedell" <bill(at)wynde(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: selecting table at execution with PL/PgSQL
Date: 2003-10-17 13:23:16
Message-ID: 020101c394b1$d336ebe0$6602a8c0@Slim
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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=#

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shridhar Daithankar 2003-10-17 13:42:24 Re: VACUUM degrades performance significantly. Database
Previous Message Cláudia Morgado 2003-10-17 13:15:35 Help or Bug?