Syntax Error in Dynamic SQL in Pl/Pgsql Function

Syntax error in dynamic SQL in pl/pgsql function

When creating a PL/pgSQL function, the function body is saved as string literal as is. Only superficial syntax checks are applied. Contained statements are not actually executed or tested on a deeper level.

However, basic syntax errors like you have in your query string would still be detected in actual SQL statements. But you are using dynamic SQL with EXECUTE. The statement is contained in a nested string literal and is your responsibility alone.

This seems to be misguided to begin with. There is no apparent reason for dynamic SQL. (Unless you have very uneven data distribution and want to force Postgres to generate a custom plan for each input value.)

If you had used a plain SQL statement, you would have gotten the error message at creation time:

CREATE OR REPLACE FUNCTION search_person(name text)  -- still incorrect!
  RETURNS TABLE(address_id integer, address_geom text, event_name text) AS
$func$
BEGIN
   RETURN QUERY
   SELECT address.id, event.name, address.geom 
   FROM  event JOIN person JOIN address JOIN person_address JOIN event_person
   WHERE 
   person_address.event_id = event.id AND
   event_person.event_id = event.id AND
   person.id = event_person.person_id AND
   person.name like $1;  -- still $1, but refers to func param now!
END
$func$  LANGUAGE plpgsql;

The SQL statement is still invalid. [INNER] JOIN requires a join condition - like Nick commented. And I don't see the need for PL/pgSQL at all. A simple SQL function should serve well:

CREATE FUNCTION search_person(name text)
  RETURNS TABLE(address_id integer, address_geom text, event_name text) AS
$func$
   SELECT a.id, a.geom, e.name  -- also fixed column order to match return type
   FROM   person         AS p
   JOIN   event_person   AS ep ON ep.person_id = p.id
   JOIN   event          AS e  ON e.id = ep.event_id
   JOIN   person_address AS pa ON pa.event_id = e.id
   JOIN   address        AS a  ON a.id = pa.address_id -- missing join condition !!
   WHERE  p.name LIKE $1;
$func$  LANGUAGE sql;

I rewrote the query to fix syntax error, using table aliases for better readability. Finally, I also added one more missing condition based on an educated guess: a.id = pa.address_id.

Now it should work.

Related:

• plpgsql function not inserting data as intended
• Difference between language sql and language plpgsql in PostgreSQL functions

Or no function at all, just use a prepared statement instead. Example:

• Split given string and prepare case statement

If you need dynamic SQL after all, pass values with the USING clause like you had it and make sure to defend against SQL injection when concatenating queries. Postgres provides various tools:

• SQL injection in Postgres functions vs prepared queries
• Define table and column names as arguments in a plpgsql function?
• Table name as a PostgreSQL function parameter

How to resolve syntax error with dynamic PostgreSQL statement

EXECUTE in plain SQL is used to run prepared statements.
Straight quote from the docs:

EXECUTE is used to execute a previously prepared statement. Since prepared statements only exist for the duration of a session, the prepared statement must have been created by a PREPARE statement executed earlier in the current session.

You're probably mixing it up with pl/pgsql EXECUTE, which is totally different.

PL/pgSQL Syntax Error at end of Input

you would need dynamic sql here, eg:

t=# create or replace function ds() returns table (i int) as
$$
declare
tn text := 'pg_database';
begin
return query execute format('select oid::int from %I',tn);
end;
$$language plpgsql
;
CREATE FUNCTION
t=# select * from ds();
     i
-----------
         1
     12945
     12950
  12963038
 111274822
  32059515
  26947889
 173381559
  32061155
  32061156
  82287221
 203004236
 214018521
(13 rows)

Any tricks to fix this syntax error in PLPGSQL?

You can't use variables as identifiers (column or table names). You need dynamic SQL for this:

execute format('CREATE ROLE %I', v_role_name);

and

execute format('GRANT EXECUTE on FUNCTION %I to %I', i, v_role_name);

How to resolve error when running dynamic PostreSQL function

I suggest to use parameters instead of bad practice of stitching strings, as follows:

CREATE OR REPLACE FUNCTION my_app.job_batch(
    huc text,
    input_list text[],
    email text
 ) RETURNS VOID AS
 $$
 DECLARE
     id text;
 BEGIN
     FOREACH id IN ARRAY input_list LOOP
         execute format ('SELECT * FROM my_app.my_funct($1, $2, $3)')
            using huc, id, email;
     END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

as shown in official docs https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Syntax error while using EXECUTE format in Postgresql Function to assign variable

Although @Lukasz solution may also work. I ended up using the implementation suggested by @stickybit in the comments of the question

Answer:

CREATE OR REPLACE FUNCTION my_function() RETURNS TRIGGER AS $$ 
DECLARE 
    cnt bigint;
BEGIN 
    IF NEW.field1 = 'DECLINED' THEN 
        cnt := ('SELECT count(*) FROM table1 WHERE field2 = NEW.field2 AND field1 != NEW.field1 AND id != NEW.id;')
        IF cnt = 0 THEN
             'UPDATE table1 SET field1 = 'DECLINED' WHERE id = NEW.field2';
         END IF;
    END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Postgresql plpgsql function syntax error

If you use plpgsql, you must include the begin / end statements and you must use RETURN QUERY ... RETURN; as per the doc.

Otherwise you can switch to using SQL instead of PLPGSQL and your function would work as is.

CREATE OR REPLACE FUNCTION getloggeom([...])
  RETURNS SETOF record AS
$BODY$
    [...]
$BODY$
  LANGUAGE SQL
  COST 1000
  ROWS 1000;

facing syntax error at or near if postgres

You're missing begin and end and a few semicolons. This should work:

create procedure learnpostgre(x integer, y integer)
language plpgsql
as $$
begin
if x > y
then
raise 'X is greater';
end if;
end
$$

EXECUTE statement syntax error in PostgreSQL

you confuse SQL execute and plpgsql execute - first to execute prepared statement and is run in SQL (as you try). second is a part of function plpgsql code

https://www.postgresql.org/docs/current/static/sql-execute.html

EXECUTE — execute a prepared statement

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Oftentimes you will want to generate dynamic commands inside your
PL/pgSQL functions, that is, commands that will involve different
tables or different data types each time they are executed. PL/pgSQL's
normal attempts to cache plans for commands (as discussed in Section
42.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:

examples:

t=# prepare s as select now();
PREPARE
t=# execute s;
              now
-------------------------------
 2017-12-14 12:47:28.844485+00
(1 row)

and plpgsql:

t=# do
$$
declare
 t text;
begin
execute 'select now()' into t;
raise info '%',t;
end;
$$
;
INFO:  2017-12-14 12:48:45.902768+00
DO

updtae

to avoid injection using dynamic code, use function format
https://www.postgresql.org/docs/current/static/functions-string.html

Format arguments according to a format string. This function is
similar to the C function sprintf.

---

Related Topics