Skip site navigation (1) Skip section navigation (2)

Re: plpgsql question

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Postgres Admin <postgres(at)productivitymedia(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org,PostgreSQL - Admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: plpgsql question
Date: 2005-08-30 16:50:14
Message-ID: 20050830165013.GA57465@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
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

In response to

Responses

pgsql-admin by date

Next:From: Joel FradkinDate: 2005-08-30 17:35:39
Subject: connections running out
Previous:From: Postgres AdminDate: 2005-08-30 16:15:54
Subject: plpgsql question

pgsql-sql by date

Next:From: Postgres AdminDate: 2005-08-30 17:59:04
Subject: Re: [SQL] plpgsql question
Previous:From: Postgres AdminDate: 2005-08-30 16:15:54
Subject: plpgsql question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group