Ребята, помогите разобраться, как запустить сие чудо:
CREATE OR REPLACE FUNCTION "public"."test"(int4, json)
RETURNS SETOF "pg_catalog"."record" AS $BODY$
DECLARE
rb boolean;
vld text;
msg text;
clmn_name text;
v text;
codes text[];
msg_param text;
BEGIN
codes := ARRAY
[
'code1'
];
for v in select unnest(codes)
loop
IF ((SELECT NULLIF(json_extract_path_text($2, 'first_param'), '') IS NOT NULL) AND
(SELECT NULLIF(json_extract_path_text($2, 'sec_param'), '') IS NOT NULL)) THEN
IF (json_extract_path_text($2, 'first_param')='2' and json_extract_path_text($2, 'sec_param')<>'9') THEN
rb := true; vld :='code1';
msg := 'hi there!';
clmn_name := '1';
RETURN NEXT rb, vld, msg, clmn_name;
END IF;
ELSE
end if;
end loop;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100
ROWS 1000
;
когда пытаюсь так:
[SQL]select * from test(0::integer, '{"first_param":"2", "sec_param":"5"}'::json)
[Err] ERROR: a column definition list is required for functions returning "record"
Когда так:
[SQL]
select * from test(0::integer, '{"first_param":"2", "sec_param":"5"}'::json) t1(rb boolean, vld text, msg text, clmn_name text)
[Err] ERROR: query "SELECT rb, vld, msg, clmn_name" returned 4 columns
CONTEXT: PL/pgSQL function test(integer,json) line 24 at RETURN NEXT
Даже пытался первую строчку заменить на
CREATE OR REPLACE FUNCTION "public"."test"(IN int4, IN json, OUT rb bool, OUT vld text, OUT msg text, IN clmn_name text)
но в этом случае - не понимаю, как ее вызвать, ибо
[SQL]select * from test(0::integer, '{"first_param":"2", "sec_param":"5"}'::json)
[Err] ERROR: function test(integer, json) does not exist
LINE 1: select * from test(0::integer, '{"first_par...
помогите разобраться, а? :-)