execute, or perform are the commands that allow you to build strings dynamically and then run them (in plpgsql).
the difference is that execute requires you to have a variable that can receive the result.
like so (it's a bit of a stupid example):
create or replace function test(p_table name) returns integer as $$
declare
t_result integer;
t_sql text;
begin
t_sql:='select count(*) from '||quote_ident(p_table);
execute t_sql into t_result;
return t_result;
end
$$ language plpgsql strict volatile;
the difference with perform is that you could just go:
perform t_sql; (but the result would be discarded).
but then, why would you want to do any of that?
there are lots of handy tables already in the system that show you what tables there are, how many records they have, etc.
I do have a little script for myself that extends that with the owner of the table and shows the comments on the table as a measure for a little extra documentation. You can change the comments by editting the table. It is calculated nightly.
It has some dutch (my native language) in it, so i'd have to translate it for you (and others reading this). I'd do that if someone asks me.
HTH
WBL
On Fri, May 18, 2012 at 12:47 PM, Nart Tamash
<nart.tamash@gmail.com> wrote:
Hi,
Is it possible to have a query that that would run on a different table every time it is executed? This query would be integrated in a trigger function that would serve a trigger that fires every time a new table is inserted in the database. This would be monitored through the individual records in a certain "index" table which would have a column that holds the table names. How can I sort of select these string values that hold the table names and cast them in actual "table names" type in my query?
All my searching leaded to the EXECUTE command but didn't quite figured out how that works when I need to return the whole table and not just a RECORD type.
My specific case:
INSERT INTO table(column)
SELECT ....
FROM varying table
Regards,
Nart
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth