I have data in one table called articles and I would like to make a
function in which takes certain data from it and display the results.
Example:
CREATE TABLE articles (
article_id serial,
title varchar(200),
posted timestamp,
article_subject varchar(200),
article_body text,
allow_comments boolean,
comments smallint
);
I understand one way to display a results I would like is creating a
TYPE with the columns needed.
CREATE TYPE articles_output AS (
article_id int
title varchar(200),
article_body text,
comments smallint
);
Now I would like the function to display data using the LIMIT and OFFSET
option
ex: SELECT title, article_body, comments FROM articles ORDER BY
article_id DESC *LIMIT 4 OFFSET 0*;
this is function I created:
CREATE OR REPLACE FUNCTION article_display(integer, integer)
RETURNS SETOF article_output AS $$
DECLARE
articleRow article_output%ROWTYPE;
sampleRow RECORD;
BEGIN
FOR sampleRow IN SELECT title, article_body, comments
FROM articles
ORDER BY article_id
DESC LIMIT $1
OFFSET $2 LOOP
RETURN NEXT sampleRow;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
this is the error ->
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "article_sample" line 10 at return next
Can I do this.... or are there better options?
Thanks for the help,
J