Your syntax is twisted in several ways. If the return type of function A
is defined as RETURNS TABLE (id int, address text)
, it could work like this:
Select A.address, B.name
FROM f_a() A
JOIN B USING (id);
Aside: While your identifiers look alright for the purpose, I strictly use legal, lower case identifiers to avoid complications.
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.
Best Answer
Answer is yes. :)
I am using
format()
with%I
to sanitize the table name and avoid SQL injection. Requires PostgreSQL 9.1 or above.Be sure to use single quotes (
''
) for data. Double quotes (""
) are for identifiers in SQL.