1

I am using PL/pgSQL in PostgreSQL 10, to create complex queries. I am testing a query with a couple of JOINs and ANDs. This is what I have so far:

DROP FUNCTION IF EXISTS search_person(name text);
CREATE  FUNCTION search_person(name text) RETURNS TABLE(address_id integer, address_geom text, event_name text) AS $$
--DECLARE 

BEGIN
    RETURN QUERY EXECUTE 
    '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'        
    
    USING name;
END;
$$
LANGUAGE plpgsql;

I get no errors while creating this function. I call it like so select search_person('nick'); and I get:

ERROR:  syntax error at or near "WHERE"
LINE 3:     WHERE 
            ^
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
CONTEXT:  PL/pgSQL function search_creator(text) line 5 at RETURN QUERY
SQL state: 42601

I tried replacing AND with || in the WHERE clause, but nothing changed.

This is the code I have now and I get an empty table, even though I should get results, according to my database data that I checked.

CREATE  FUNCTION search_person(name character(600)) RETURNS TABLE(address_id bigint, address_geom geometry, event_name character(200)) AS $$

BEGIN
    RETURN QUERY EXECUTE 
    'SELECT address.id, address.geom, event.name        

    FROM
    person 
    JOIN event_creator ON event_person.person_id = person.id
    JOIN event ON event.id = event_person.event_id 
    JOIN person_address ON person_address.event_id = event.id 
    JOIN address ON address.id = cep.address_id
    
    WHERE person.name LIKE $1'
    USING name;
    
END;
$$
LANGUAGE plpgsql;

What should I do?

Improve this question
4
  • 4
    Your JOIN clauses have no join conditions. See the docs for the correct syntax. Commented Jan 21, 2018 at 22:34
  • @NickBarnes that's ANSI-89 join syntax, which is valid but dangerous. One of the dangers, which is made quite clear from this query, is that there is no JOIN predicate for the table address, meaning there will be a Cartesian product of event and address. @OP, what is the JOIN condition of the address table? Commented Jan 21, 2018 at 22:38
  • Correction, it seems PostgreSQL have ditched ANSI-89 support, so your comment is correct on the malformed JOIN syntax Commented Jan 21, 2018 at 22:40
  • @e_i_pi.no, Postgres has not "ditched" support for the old implicit join conditions in the WHERE clause. But in this case it's an invalid mix of the explicit JOIN operator and the implicit join conditions. Commented Jan 22, 2018 at 7:00

1 Answer 1

Reset to default
2

The function body of a PL/pgSQL function is saved as string literal as is. Only superficial syntax checks are run at creation time. Contained statements are not actually executed or tested on a deeper level.

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 passed in a nested string literal and 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.)

A plain SQL statement would have generated the error message immediately:

CREATE OR REPLACE FUNCTION search_person(name text)  -- still incorrect!
  RETURNS TABLE(address_id integer, address_geom text, event_name text)
  LANGUAGE plpgsql 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;  -- this $1 refers to function parameter!
END
$func$;

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)
  LANGUAGE sql AS
$func$
SELECT a.id, a.geom, e.name  -- column order matching 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$;

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

Now it should work.

Related:

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

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Thanks for the answer. Coming from a mySQL background, I forgot the JOIN-AS part. Also, about the "no apparent reason for dynamic SQL" : I am new to pl/pgsql, so this is a test and when it works as is, then I will add more parameters that the user may or may not choose and form the FROM and WHERE parts with IFs. It will be an all-in-one query. I am trying to create something like this, but for more tables and parameters.
According to you and what I saw in the error warning "the function body is saved as string literal as is". That means no prepared statements for dynamic parameters and EXECUTE, right? So, practically no safety at all. Since my queries will contain user input, I guess I should abandon pl/pgsql and concatenate prepared statements before executing them. Right?
...about EXECUTE safety, I am checking your answers and the postgre documentation now...
@slevin: You do not need quote_literal() or quote_ident() with format(), which has built-in quoting capabilities. It's one or the other, not both. About your empty result: with a probability bordering on certainty you don't want to use character(600) etc. See: stackoverflow.com/a/20334221/939860 If doubts remains, start a new question, where you also provide table definition, sample values and a description of what your query is supposed to achieve exactly.
@slevin: Here. It's a system catalog in the schema pg_catalog. Don't mess with it.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.