1

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?

  • .... and the error is? Please show the exact text of the error message. – Craig Ringer Aug 24 '15 at 13:26
  • 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}' – spencerrecneps Aug 24 '15 at 13:52
  • 1
    it has to be = ANY (or some other operator) – Craig Ringer Aug 24 '15 at 14:59
2

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.

  • 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. – spencerrecneps Aug 24 '15 at 17:19
  • @spencerrecneps: Plus, you need the parentheses, too: id = ANY ('{1,2,3}'::int[]). – Erwin Brandstetter Aug 24 '15 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.