Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL
От | Tom Lane |
---|---|
Тема | Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL |
Дата | |
Msg-id | 17567.1005077986@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL ("Jon Obuchowski" <jon_obuchowski@terc.edu>) |
Список | pgsql-sql |
"Jon Obuchowski" <jon_obuchowski@terc.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
В списке pgsql-sql по дате отправления:
Предыдущее
От: Alex PilosovДата:
Сообщение: Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL