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

Поиск
Список
Период
Сортировка
От Jon Obuchowski
Тема trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL
Дата
Msg-id 85256AFC.0060FA3E.00@procyon.terc.edu
обсуждение исходный текст
Ответы Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL  (Alex Pilosov <alex@pilosoft.com>)
Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
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




В списке pgsql-sql по дате отправления:

Предыдущее
От: Fernando Nasser
Дата:
Сообщение: Re: design tool
Следующее
От: "Josh Berkus"
Дата:
Сообщение: Re: Left join error