Re: SELECT FROM 'varying_table'

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(dot)tamash(at)gmail(dot)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