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

"Jon Obuchowski" <jon_obuchowski(at)terc(dot)edu> writes:
> Anyway, I keep encountering parsing errors within the EXECUTE query LOOP
> construct; my code follows:

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

The trouble here is that FOR ... IN EXECUTE is defined to take a record
or row variable as the FOR loop variable. Since you have an integer
variable there, it thinks it's an integer FOR loop.

It is more than a little bit bletcherous that the syntaxing of FOR
depends on the type of variable mentioned :-( ... but the plpgsql
parser has a lot of bletcherousness as far as error detection and
recovery goes. Jan's been heard to say that he'd like to throw it
away and start over; I agree.

Anyway, try doing it this way:

crec RECORD;

FOR crec IN EXECUTE key_check_query LOOP
IF crec.check_count > 0 THEN

BTW, one way to debug this sort of thing for yourself is to enable
query logging and look in the postmaster's log file. I did

export PGOPTIONS="-d2"
psql ...

create and execute function per your example

and found in the log:

DEBUG: StartTransactionCommand
DEBUG: query: SELECT check_foreign_key ( 'test', 'test_id', 1);
DEBUG: ProcessQuery
DEBUG: query: SELECT 'f'
DEBUG: query: SELECT ( 'SELECT COUNT(*) AS check_count FROM ' || quote_ident( $1 ) || ' WHERE ' || quote_ident( $2 ) || ' = ' || quote_literal( $3 ) || ';' )
DEBUG: query: SELECT EXECUTE $1 LOOP IF $2 > 0 THEN $3 := 't'
ERROR: parser: parse error at or near "$1"
NOTICE: Error occurred while executing PL/pgSQL function check_foreign_key
NOTICE: line 21 at for with integer loopvar
DEBUG: AbortCurrentTransaction

which told me that the EXECUTE wasn't getting recognized as a keyword,
and from there I could work out why. This particular example is maybe
too weird for the average person to work out without help, but I've
found that most unexplainable "syntax errors" in plpgsql can be debugged
fairly easily if you look to see what queries plpgsql is feeding down
to the main SQL engine.

regards, tom lane