Message-ID: <43149E68.8030600@productivitymedia.com>
Date: Tue, 30 Aug 2005 13:59:04 -0400
From: Postgres Admin <postgres@productivitymedia.com>
User-Agent: Thunderbird 1.6a1 (Macintosh/20050829)
MIME-Version: 1.0
Cc: pgsql-sql@postgresql.org, PostgreSQL - Admin <pgsql-admin@postgresql.org>
Subject: Re: [SQL] plpgsql question
References: <BAY101-DAV8EA3AA3EF2A17C6F7C9EEA3B60@phx.gbl>
	<F104769F-4833-4E10-892B-8EDCC0AE0E27@sitening.com>
	<BAY101-DAV106551B6BE74061AD63302A3AF0@phx.gbl>
	<1C7C5DE3-5EB5-4EB8-A187-290EF5081BD7@sitening.com>
	<4314863A.3030107@productivitymedia.com>
	<20050830165013.GA57465@winnie.fuhr.org>
In-Reply-To: <20050830165013.GA57465@winnie.fuhr.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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