Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Not sure I understand what you're trying to do, but wouldn't a view work? –  Mat Dec 31 '12 at 18:58
    
Cross post with different alias: stackoverflow.com/questions/14105101/… –  Erwin Brandstetter Dec 31 '12 at 20:56
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks Matt and Happy NY! –  random joiner Dec 31 '12 at 19:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.