Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL

Your problem is trying to use arguments of the function inside something
that is EXECUTE'd. Try stuffing argument's values (NOT their names) inside
the SQL.

On Tue, 6 Nov 2001, Jon Obuchowski wrote:

> I've been playing around with PL/pgSQL, and in order to learn about using
> EXECUTE I decided to create a generic check constraint function for use in
> verifying foreign keys crossing inherited tables (yes, this will perform
> poorly vs. a hard-coded query, but it's intended for learning, not
> production).
>
> Anyway, I keep encountering parsing errors within the EXECUTE query LOOP
> construct; my code follows:
>
> DROP FUNCTION check_foreign_key ( varchar(32), varchar(32), integer );
>
> CREATE FUNCTION check_foreign_key ( varchar(32), varchar(32), integer )
> RETURNS text AS '
> DECLARE
> table_name ALIAS FOR $1;
> field_name ALIAS FOR $2;
> field_value ALIAS FOR $3;
> key_check_query TEXT;
> check_count INTEGER;
> field_value_exists BOOLEAN := ''f'';
>
> BEGIN
> key_check_query :=
> (
> ''SELECT COUNT(*) AS check_count FROM ''
> || quote_ident(table_name)
> || '' WHERE ''
> || quote_ident(field_name)
> || '' = ''
> || quote_literal(field_value)
> || '';''
> );
>
> FOR check_count IN EXECUTE key_check_query LOOP
> IF check_count > 0 THEN
> field_value_exists := ''t'';
> END IF;
> EXIT;
> END LOOP;
>
> RETURN field_value_exists;
> END;
> ' LANGUAGE 'plpgsql';
>
> this "compiles" OK upon creation, but when I try to execute it against a
> specific table and field...
> SELECT check_foreign_key ( 'test', 'test_id', 1);
>
> I get the following parsing error:
> ERROR: parser: parse error at or near "$1"
>
> However, if I simplify the loop construct into a simple (and useless)
> EXECUTE, then the function compiles and returns A-OK...
> DROP FUNCTION check_foreign_key ( varchar(32), varchar(32), integer );
>
> CREATE FUNCTION check_foreign_key ( varchar(32), varchar(32), integer )
> RETURNS text AS '
> DECLARE
> table_name ALIAS FOR $1;
> field_name ALIAS FOR $2;
> field_value ALIAS FOR $3;
> key_check_query TEXT;
> check_count INTEGER;
> field_value_exists BOOLEAN := ''f'';
>
> BEGIN
> key_check_query :=
> (
> ''SELECT COUNT(*) AS check_count FROM ''
> || quote_ident(table_name)
> || '' WHERE ''
> || quote_ident(field_name)
> || '' = ''
> || quote_literal(field_value)
> || '';''
> );
>
> EXECUTE key_check_query;
>
> RETURN field_value_exists;
> END;
> ' LANGUAGE 'plpgsql';
>
> SELECT check_foreign_key ( 'test', 'test_id', 1);
>
> check_foreign_key
> -----------------
> f
> (1 row)
>
> ...so, I'm assuming that the issue lies with the FOR...IN EXECUTE LOOP, but
> I am simply failing to spot the issue.
>
> I couldn't find any decent example of using EXECUTE in the archives (though
> my attempts were hobbled somewhat by the problems with the archive search
> feature), so I'd really appreciate any pointers for using EXECUTE within
> PL/pgSQL loops.
>
> thanks,
> Jon Obuchowski
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>