Thread: Fwd: Re: question - plpgsql and query on table given by variable

Thank you for your reply. It helped me, but I need know some results. So I tryed use SELECT INTO in EXECUTE string and
itdo not work. Also I try this: 

CREATE OR REPLACE FUNCTION n(varchar) RETURNS varchar AS $$
DECLARE
 a varchar;
BEGIN
 a := $1;
 PERFORM * FROM b;
 EXECUTE 'PERFORM AsText(the_geom) FROM ' || a || ';';
 IF FOUND THEN
 RETURN 'yes';
 END IF;
 IF FOUND THEN
 RETURN 'no';
 END IF;
 RETURN 'I do not know';
END;
$$ LANGUAGE plpgsql;

Error:
SELECT n('b');
ERROR: syntax error at or near "PERFORM" at character 1
QUERY: PERFORM AsText(the_geom) FROM b;
CONTEXT: PL/pgSQL function "n" line 7 at execute statement
LINE 1: PERFORM AsText(the_geom) FROM b;

When I use PPERFORM in normal way, it works. How I can reach some results from the query executed by EXECUTE?

Thanks for your help

On Mon, Aug 29, 2005 at 11:13:15AM +0200,  Jaromír Kamler wrote:
>
>  EXECUTE 'PERFORM AsText(the_geom) FROM ' || a || ';';

PERFORM is a PL/pgSQL command that isn't valid in an EXECUTE context.
Also, be careful when interpolating variables into an EXECUTE command;
see the comments about using quote_ident() and quote_literal() in
the "Executing Dynamic Commands" documentation.  And the command
string doesn't need a terminating semicolon.

>  IF FOUND THEN
>  RETURN 'yes';
>  END IF;
>  IF FOUND THEN
>  RETURN 'no';
>  END IF;

Do you mean IF NOT FOUND?  Also, using ELSIF (or ELSEIF) might be
more readable.

> SELECT n('b');
> ERROR: syntax error at or near "PERFORM" at character 1
> QUERY: PERFORM AsText(the_geom) FROM b;
> CONTEXT: PL/pgSQL function "n" line 7 at execute statement
> LINE 1: PERFORM AsText(the_geom) FROM b;
>
> When I use PPERFORM in normal way, it works. How I can reach some
> results from the query executed by EXECUTE?

The "Executing Dynamic Commands" documentation mentions a couple
of ways (FOR-IN-EXECUTE and OPEN-FOR-EXECUTE).

--
Michael Fuhr