PostgreSQL Return Joined Results of Select

plpgsqlpostgresql

I'm learning PostgreSQL and was wondering if I can put long selects in stored procedures like this one:

CREATE OR REPLACE FUNCTION api.book_list()
RETURNS TABLE (
  id         BIGINT,
  published  DATE,
  category   VARCHAR,
  author     VARCHAR,
  name       VARCHAR
) AS $$
BEGIN
  SELECT
    b.id,
    b.published,
    c.name AS category,
    b.author,
    b.name
  FROM
    schemas.book AS b
  JOIN
    schemas.category AS c
  ON
    c.id = b.category_id;
END;
$$
LANGUAGE plpgsql;

It is supposed to return select from two joined tables.

It gives the following error:

select * from api.book_list();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "book_list" line 3 at SQL statement

Best Answer

Use RETURN QUERY SELECT ...;.

PostgreSQL is complaining because you aren't specifying what to return. Also, since you aren't actually passing parameters to the function this could also be accomplished by a view.

Related Solutions

PostgreSQL stored procedure to return rows or empty set

You can do something along the following lines:

test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer)
RETURNS SETOF integer AS
$body$
BEGIN
    CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS
    SELECT a
    FROM generate_series(1, cnt) t(a);

    IF (SELECT count(1) FROM tmp_container) > 5
    THEN
        RETURN QUERY SELECT a FROM tmp_container;
    END IF;
END;
$body$
LANGUAGE plpgsql;


test=> SELECT * FROM temptabl(4);
 temptabl 
----------
(0 rows)

test=> SELECT * FROM temptabl(6);
 temptabl 
----------
        1
        2
        3
        4
        5
        6
(6 rows)

This way you have to perform your original query only once. All other statements work on the temporary table.

Postgresql – Wrong return results

Your first query is equivalent of the following (notice the parentheses in the WHERE clause):

SELECT 
    host,
    plugin_id,
    min(cve) as cve,
    risk,
    synopsis,
    description,
    solution, 
    count(distinct(plugin_id)) 
from internal_network 
where 
    (risk in ('Critical','High') and description like '%password%') 
    or synopsis like '%password%' 
    or solution like '%password%' 
    or cve like '%password%' 
group by risk, plugin_id, host, synopsis, description, solution;

This is because the AND operator has higher precedence than the OR, that is, it 'glues' its operands stronger. I think what you wanted is

...
where 
    risk in ('Critical','High') 
    and 
    (
    description like '%password%' 
    or synopsis like '%password%' 
    or solution like '%password%' 
    or cve like '%password%' 
    )
...

instead.

Unless you have only ANDs (or ORs), it is always adviseable to use parentheses. It helps understand the expression, especially if it is formatted correctly.

Best Answer

Related Solutions

PostgreSQL stored procedure to return rows or empty set

Postgresql – Wrong return results

Related Question