Syntax: PostgreSQL with lateral join to stored procedure

Question

PostgreSQL 11.1

Where I have a procedure defined as:

FUNCTION Copy_Name( to_keep_id integer, to_delete_id integer, parent_table text) RETURNS VOID

Can I use it with a cte like:

WITH _in (to_keep_name, to_del_name) AS (
    VALUES ('tom', 'bob')
),
_to (keep_name_id, del_name_id)  AS (
    SELECT keep_name.id, del_name.id
    FROM _in
    JOIN tempA keep_name ON (keep_name.name = _in.to_keep_name)
    JOIN tempA del_name ON  (del_name.name = _in.to_del_name)
)
SELECT * 
FROM _to
JOIN LATERAL Copy_Name(_to.keep_name_id, _to.del_name_id, 'tempA') ON true;

Is there a preferred syntax for joins to a procedure?

Your function returns `VOID`. Why would you want to use it in a query? That doesn't seem useful. — Gordon Linoff, Apr 26 '20 at 01:51
@GordonLinoff Just trying to run the stored procedure alongside the cte. (It does work btw). Note: The stored procedure operates on related files. I don't need any output. — Alan Wayne, Apr 26 '20 at 03:08
@GordonLinoff: Using such function in a query makes sense to call it N times with the values produced by the query. It's a bit of an abuse of SQL for the side effects, but it works. A couple of other things in the question don't make sense. — Erwin Brandstetter, Apr 26 '20 at 14:18

Erwin Brandstetter · Accepted Answer · 2020-04-26T14:19:54.333

Procedures cannot be nested in outer queries at all.

But I see a function here, and no procedure. Since it returns void, you can just place it in the SELECT list, remove the CTE and since you "don't need any output" your query burns down to something like:

SELECT copy_name(keep_name.id, del_name.id, 'tempA')
FROM   tempA keep_name
JOIN   tempA del_name ON keep_name.name = 'tom'
                     AND del_name.name = 'bob';

Returns a NULL value for each function call. (The effective cross-self-join seems odd, though.)

Else you'd need a LEFT JOIN to make sense:

SELECT * 
FROM _to
LEFT JOIN LATERAL copy_name(_to.keep_name_id, _to.del_name_id, 'tempA') ON true;

See:

What is the difference between LATERAL and a subquery in PostgreSQL?

Aside: Postgres 11.1? See https://www.postgresql.org/support/versioning/

score 1 · Answer 2 · answered Apr 26 '20 at 13:49

I would prefer to make it a CROSS JOIN and then drop the ON true. Or just use a comma join.

In this case the LATERAL is a noise word, as function calls are implicitly lateral. I would probably drop that as well, unless I thought in the future I (or whoever is reading the code) will not now about this feature. If I were writing for coding amateurs to read, I might keep it.

Syntax: PostgreSQL with lateral join to stored procedure

2 Answers2