Thread: How do I get query result(select e.g.) from a dynamic command(EXECUTE)?

Hi,

I want to do a select in dynamic command, something like:

TRIGGER FUNCTION
DECLARE
    table_name_suffix text;
    temp_result RECORD;
    temp_result2 RECORD;

...

BEGIN
    --initialization of table_name_suffix(from TG_RELNAME e.g.)


    select into temp_result from
''fixed_table_name_prefix''||table_name_suffix where another_table_name =
abc;

    --Then I want to use part of the result as part of the table name for my
next query
    select into temp_result2 from
''fixed_table_name_prefix''||temp_result.anothertablename;

END

I know that SELECT INTO doesn't support dynamic command, EXECUTE does. But
if I EXECUTE a select query, how do I get
the select result from it?

Many thanks,

Wei Wang

Sorry. I just found it in the documentation. FOR-IN-EXECUTE or
OPEN-FOR-EXECUTE.


----- Original Message -----
From: "Wei Wang" <ww220@cam.ac.uk>
To: "pgsql" <pgsql-general@postgresql.org>
Sent: Thursday, February 19, 2004 11:50 AM
Subject: [GENERAL] How do I get query result(select e.g.) from a dynamic
command(EXECUTE)?


> Hi,
>
> I want to do a select in dynamic command, something like:
>
> TRIGGER FUNCTION
> DECLARE
>     table_name_suffix text;
>     temp_result RECORD;
>     temp_result2 RECORD;
>
> ...
>
> BEGIN
>     --initialization of table_name_suffix(from TG_RELNAME e.g.)
>
>
>     select into temp_result from
> ''fixed_table_name_prefix''||table_name_suffix where another_table_name =
> abc;
>
>     --Then I want to use part of the result as part of the table name for
my
> next query
>     select into temp_result2 from
> ''fixed_table_name_prefix''||temp_result.anothertablename;
>
> END
>
> I know that SELECT INTO doesn't support dynamic command, EXECUTE does. But
> if I EXECUTE a select query, how do I get
> the select result from it?
>
> Many thanks,
>
> Wei Wang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

On Thursday 19 February 2004 11:50, Wei Wang wrote:
> Hi,
>
> I want to do a select in dynamic command, something like:
>
[snip]
> I know that SELECT INTO doesn't support dynamic command, EXECUTE does. But
> if I EXECUTE a select query, how do I get
> the select result from it?


FOR record | row IN EXECUTE text_expression LOOP
    statements
END LOOP;

It's in the manuals, but that's about all they say on the subject
--
  Richard Huxton
  Archonet Ltd