Postgresql – Passing array from one plpgsql function to another

arraydynamic-sqlparameterplpgsqlpostgresql

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?

Best Answer

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.

Related Question