From: Michael Fuhr <mike@fuhr.org>
To: Postgres Admin <postgres@productivitymedia.com>
Cc: pgsql-sql@postgresql.org, PostgreSQL - Admin <pgsql-admin@postgresql.org>
Subject: Re: plpgsql question
Date: Tue, 30 Aug 2005 10:50:14 -0600
Message-ID: <20050830165013.GA57465@winnie.fuhr.org> (raw)
In-Reply-To: <4314863A.3030107@productivitymedia.com>
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>
On Tue, Aug 30, 2005 at 12:15:54PM -0400, Postgres Admin wrote:
>
> Can I do something like this:
It's good that you gave an example, but it would also be good to
give a summary of what you're trying to do and what trouble you're
having so people don't have to guess.
> CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER);
> INSERT INTO sample(node,parent) VALUES(1,0);
> INSERT INTO sample(node,parent) VALUES(2,0);
> INSERT INTO sample(node,parent) VALUES(3,1);
> INSERT INTO sample(node,parent) VALUES(4,3)
>
> CREATE OR REPLACE FUNCTION article_display(anyelement, anyelement)
> RETURNS SETOF samle AS $$
I assume you want to return "SETOF sample", not "samle". When
posting code please post the actual code you're running so typos
don't distract from the real problem (unless a typo *is* part of
the problem).
> DECLARE
> articleRow sample%ROWTYPE;
> BEGIN
> FOR articleRow IN SELECT comments
> FROM theirry.articles
You're selecting a column of one table (theirry.articles) into a
row type variable of another table (sample). If the value of
comments can't be converted to an integer (the type of sample's
first column) then you'll get a syntax error. And what about the
other columns of sample? What are you really trying to do here?
> ORDER BY article_id
> DESC LIMIT $1
> OFFSET $2 LOOP
If you're using the function's arguments like this, why did you
declare them as anyelement instead of integer?
> RETURN NEXT articleRow;
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
It's not clear what you're trying to do nor what problems you're
having. Is this example real or a contrived facsimile of what
you're really trying to do? Could you provide some more information?
--
Michael Fuhr
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, postgres@productivitymedia.com, pgsql-admin@postgresql.org
Subject: Re: plpgsql question
In-Reply-To: <20050830165013.GA57465@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox