PostgreSQL - SQL state: 42601 syntax error

Question

I would like to know how to use a dynamic query inside a function. I've tried lots of ways, however, when I try to compile my function a message SQL 42601 is displayed.

The code that I use:

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS 
$$
BEGIN
  WITH v_tb_person AS (return query execute sql)
  select name, count(*) from v_tb_person where nome like '%a%' group by name
  union
  select name, count(*) from v_tb_person where gender = 1 group by name;
END     
$$ LANGUAGE plpgsql;

Error message I receive:

ERROR:  syntax error at or near "return"
LINE 5:     WITH v_tb_person AS (return query execute sql)

I tried using:

WITH v_tb_person AS (execute sql)

WITH v_tb_person AS (query execute)

WITH v_tb_person AS (return query execute)

What is wrong? How can I solve this problem?

Its a question related to PostgreSQL equivalent of Oracle “bulk collect”

This is clearly plpgsql syntax. What is the `[Oracle]` tag doing here? — Erwin Brandstetter, Apr 30 '13 at 05:29
@ErwinBrandstetter Previous question was about translating PL/SQL to PL/PgSQL, so probably carried that over. I've removed the tag. — Craig Ringer, Apr 30 '13 at 05:31
One thing that can be well worth trying is the documentation. A lot of effort goes into the PostgreSQL (and PL/PgSQL) documentation. In this case, you'll want to start with [Dynamic statements in PL/PgSQL](http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN). — Craig Ringer, Apr 30 '13 at 05:42

Erwin Brandstetter · Accepted Answer · 2022-12-11T01:53:04.327

Your function would work like this:

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
  RETURNS TABLE (name text, rowcount int)
  LANGUAGE plpgsql AS 
$func$
BEGIN
   RETURN QUERY EXECUTE '
   WITH v_tb_person AS (' || sql || $x$)
   SELECT name, count(*)::int FROM v_tb_person WHERE nome LIKE '%a%' GROUP BY name
   UNION
   SELECT name, count(*)::int FROM v_tb_person WHERE gender = 1 GROUP BY name$x$;
END     
$func$;

Call:

SELECT * FROM prc_tst_bulk($$SELECT a AS name, b AS nome, c AS gender FROM tbl$$)

You cannot mix plain and dynamic SQL the way you tried to do it. The whole statement is either all dynamic or all plain SQL. So I am building one dynamic statement to make this work. You may be interested in the chapter about executing dynamic commands in the manual.

The aggregate function count() returns bigint, but you had rowcount defined as integer, so you need an explicit cast ::int to make this work.

I use dollar quoting to avoid quoting hell.

However, is this supposed to be a honeypot for SQL injection attacks or are you seriously going to use it? For your very private and secure use, it might be ok-ish - though I wouldn't even trust myself with a function like that. If there is any possible access for untrusted users, such a function is a loaded footgun. It's impossible to make this secure.

Craig (a sworn enemy of SQL injection) might get a light stroke when he sees what you forged from his answer to your preceding question. :)

The query itself seems rather odd, btw. The two SELECT terms might be merged into one. But that's beside the point here.

I actually think this kind of thing is *less* bad than accepting unchecked params. If you're accepting whole raw SQL statements you already know your inputs have to come from an utterly trusted source or a source with the same priv level the function is executing as. Now, if it the function was `SECURITY DEFINER` too... — Craig Ringer, Apr 30 '13 at 05:34
@CraigRinger: All in all, I hope I didn't completely misquote you with my remark. In which case I should remove it. — Erwin Brandstetter, Apr 30 '13 at 05:38
Nah, it's all good. It still gives me the twitches I assure you. — Craig Ringer, Apr 30 '13 at 05:41

PostgreSQL - SQL state: 42601 syntax error

1 Answers1