Re: [SQL] [ADMIN] plpgsql question

[Postgres Admin]

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend