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

From: "Jon Obuchowski" <jon_obuchowski(at)terc(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL
Date: 2001-11-06 17:57:06
Message-ID: 85256AFC.0060FA3E.00@procyon.terc.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2001-11-06 18:18:13 Re: Left join error
Previous Message Mark Nickel 2001-11-06 17:08:15 Re: copy command with PSQL