Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL - Mailing list pgsql-sql

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
> 
>