Обсуждение: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL
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
ALIASFOR $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
ALIASFOR $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
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 > >
"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
Hi Jon, the field for a loop must be from type record. I have insert some line, but not testet. ( No time to do this ) > 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''; -------> rec RECORD; > > BEGIN > key_check_query := > ( > ''SELECT COUNT(*) AS check_count FROM '' > || quote_ident(table_name) > || '' WHERE '' > || quote_ident(field_name) > || '' = '' > || quote_literal(field_value) > || '';'' > ); > > FOR rec IN EXECUTE key_check_query LOOP --------> ^^^ > IF rec.check_count > 0 THEN --------> ^^^ > field_value_exists := ''t''; > END IF; > EXIT; > END LOOP; > > RETURN field_value_exists; > END; > ' LANGUAGE 'plpgsql'; Regards, alexander -- ------------------------------------------------------------------------- Nextra Deutschland | Alexander Kunz GmbH & Co. KG | Capacity Planning & Optimization Region Mitte | Tel.: +49 (0)6151 88008-897 Birkenweg 14a | Fax: +49 (0)6151 88008-500 64295 Darmstadt | Mobil: +49 (0)175 9309601 http://www.nextra.de | E-Mail: alexander.kunz@nextra.de -------------------------------------------------------------------------