Passing array from one plpgsql function to another

Question

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new table.

Parent function:

CREATE OR REPLACE FUNCTION foo(linktable REGCLASS, 
 inttable REGCLASS, 
 verttable REGCLASS)
DECLARE
 intersection_ids INT[];
-- ... SNIP
 BEGIN
 EXECUTE format('SELECT array_agg(id) from %s',inttable) INTO intersection_ids;
 EXECUTE format('
 SELECT tdgSetTurnInfo(%L,%L,%L,%L);
 ', linktable,
 inttable,
 verttable,
 intersection_ids);
 END;
-- SNIP ...

Child function:

CREATE OR REPLACE FUNCTION bar(linktable REGCLASS,
 inttable REGCLASS,
 verttable REGCLASS,
 intersection_ids INT[])
-- ... SNIP
 EXECUTE format('
 INSERT INTO %s (int_id,
 ref_link_id,
 match_link_id,
 ref_azimuth,
 match_azimuth)
 SELECT int.id,
 l1.id,
 l2.id,
 degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
 degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
 FROM %s int,
 %s v1,
 %s v2,
 %s l1,
 %s l2
 WHERE int.id ANY %L
 AND int.id = v1.intersection_id
 AND int.id = v2.intersection_id
 AND l1.target_node = v1.node_id
 AND l2.source_node = v2.node_id
 AND l1.road_id IS NOT NULL
 AND l2.road_id IS NOT NULL
 AND l1.road_id != l2.road_id;
 ', temptable,
 inttable,
 verttable,
 verttable,
 linktable,
 linktable,
 intersection_ids);
-- SNIP ...

When I run this code I get an error:

QUERY:

INSERT INTO tdggtitemptbl (int_id,
 ref_link_id,
 match_link_id,
 ref_azimuth,
 match_azimuth)
SELECT int.id,
 l1.id,
 l2.id,
 degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
 degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
FROM a_intersections int,
 a_net_vert v1,
 a_net_vert v2,
 a_net_link l1,
 a_net_link l2
WHERE int.id ANY '{1,2,3,4,5,6,7,8,9,10,11,12,13}'
AND int.id = v1.intersection_id
AND int.id = v2.intersection_id
AND l1.target_node = v1.node_id
AND l2.source_node = v2.node_id
AND l1.road_id IS NOT NULL
AND l2.road_id IS NOT NULL
AND l1.road_id != l2.road_id;

However, when I run similar code outside of the function and without the format() statement it runs without issues. Is there a problem with running my INT[] array through the format()? Or is it something else entirely?

• Craig Ringer: .... and the error is? Please show the exact text of the error message.
• spencerrecneps: Apologies. I thought I pasted the error with the rest of the output. I'll have to update when I'm back at my other machine. I believe it was a syntax error. It referenced this line specifically: int.id ANY '{1,2,3,4,5,6,7,8,9,10,11,12,13}'
• Craig Ringer: it has to be = ANY (or some other operator)

Answer 1

You can avoid various complications by passing values as values with the USING clause:

CREATE OR REPLACE FUNCTION foo(linktable regclass, 
 inttable regclass, 
 verttable regclass)
RETURNS void AS
$func$
BEGIN
 EXECUTE format(
 'SELECT tdgSetTurnInfo(1,ドル 2,ドル 3,ドル array_agg(t.id)) FROM %s t'
 , linktable)
 USING 1,ドル 2,ドル 3ドル; -- you could use parameter names as well
END
$func$ LANGUAGE plpgsql;

And pass the generated array directly in a single SELECT.

Note that 1,ドル 2,ドル 3ドル in EXECUTE reference the USING clause and only happen to be in sync with ordinal numbers of function parameters in USING 1,ドル 2,ドル 3ドル. Same notation, independent scope!

After fixing the immediate cause for the error (like @Craig commented):

(削除) WHERE int.id ANY %L (削除ここまで)

WHERE int.id = ANY (%L)

Simplify the 2nd function in a similar fashion:

CREATE OR REPLACE FUNCTION bar(linktable regclass,
 inttable regclass,
 verttable regclass,
 intersection_ids int[])
 -- ... SNIP
 EXECUTE format('
 INSERT INTO %1$s
 (int_id, ref_link_id, match_link_id, ref_azimuth, match_azimuth)
 SELECT int.id, l1.id , l2.id
 , degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom)))
 degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
 FROM %2$s int
 JOIN %3$s v1 ON v1.intersection_id = int.id
 JOIN %3$s v2 ON v2.intersection_id = int.id
 JOIN %4$s l1 ON l1.target_node = v1.node_id
 JOIN %4$s l2 ON l2.source_node = v2.node_id
 AND l2.road_id <> l1.road_id
 WHERE int.id = ANY (1ドル) -- fix syntax and pass as value!
 AND l1.road_id IS NOT NULL
 AND l2.road_id IS NOT NULL
 ', temptable,
 inttable,
 verttable,
 linktable)
 USING intersection_ids;
 ...

The whole setup might be simplified further.

• spencerrecneps: Thanks. I could swear it worked without the '=' operator when I tested outside of the function. I must have accidentally deleted it at some point.
• Erwin Brandstetter: @spencerrecneps: Plus, you need the parentheses, too: id = ANY ('{1,2,3}'::int[]).