PostgreSQL - SQL state: 42601 syntax error

163,650

Your function would work like this:

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS 
$$
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     
$$ LANGUAGE plpgsql;

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 piece of code in the answer to your preceding question. :)

The query itself seems rather odd, btw. But that's beside the point here.

Share:
163,650
Leandro
Author by

Leandro

Updated on July 05, 2022

Comments

  • Leandro
    Leandro almost 2 years

    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”

  • Craig Ringer
    Craig Ringer about 11 years
    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...
  • Erwin Brandstetter
    Erwin Brandstetter about 11 years
    @CraigRinger: All in all, I hope I didn't completely misquote you with my remark. In which case I should remove it.
  • Craig Ringer
    Craig Ringer about 11 years
    Nah, it's all good. It still gives me the twitches I assure you.