pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql question
4+ messages / 2 participants
[nested] [flat]
* plpgsql question
@ 2005-08-30 16:15 Postgres Admin <postgres@productivitymedia.com>
2005-08-30 16:50 ` Re: plpgsql question Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Postgres Admin @ 2005-08-30 16:15 UTC (permalink / raw)
To: pgsql-sql@postgresql.org; +Cc: pgsql-admin
Can I do something like this:
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 $$
DECLARE
articleRow sample%ROWTYPE;
BEGIN
FOR articleRow IN SELECT comments
FROM theirry.articles
ORDER BY article_id
DESC LIMIT $1
OFFSET $2 LOOP
RETURN NEXT articleRow;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Thanks,
J
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql question
2005-08-30 16:15 plpgsql question Postgres Admin <postgres@productivitymedia.com>
@ 2005-08-30 16:50 ` Michael Fuhr <mike@fuhr.org>
2005-08-30 17:59 ` Re: [SQL] plpgsql question Postgres Admin <postgres@productivitymedia.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2005-08-30 16:50 UTC (permalink / raw)
To: Postgres Admin <postgres@productivitymedia.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] plpgsql question
2005-08-30 16:15 plpgsql question Postgres Admin <postgres@productivitymedia.com>
2005-08-30 16:50 ` Re: plpgsql question Michael Fuhr <mike@fuhr.org>
@ 2005-08-30 17:59 ` Postgres Admin <postgres@productivitymedia.com>
2005-08-31 02:32 ` Re: [SQL] plpgsql question Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Postgres Admin @ 2005-08-30 17:59 UTC (permalink / raw)
To: ; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] plpgsql question
2005-08-30 16:15 plpgsql question Postgres Admin <postgres@productivitymedia.com>
2005-08-30 16:50 ` Re: plpgsql question Michael Fuhr <mike@fuhr.org>
2005-08-30 17:59 ` Re: [SQL] plpgsql question Postgres Admin <postgres@productivitymedia.com>
@ 2005-08-31 02:32 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-08-31 02:32 UTC (permalink / raw)
To: Postgres Admin <postgres@productivitymedia.com>; +Cc: pgsql-admin
On Tue, Aug 30, 2005 at 01:59:04PM -0400, Postgres Admin wrote:
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "article_sample" line 10 at return next
You don't show what you did to get this error, but I'd guess you
called the function like this:
SELECT article_display(10, 0); -- wrong
You should have called it like this:
SELECT * FROM article_display(10, 0);
The function as posted has a few other problems, like returning the
wrong type (you return a RECORD variable instead of an article_output
variable) and not selecting enough columns for the declared return
type. The return type also doesn't match the type shown in the
CREATE TYPE statement, and the CREATE TYPE statement fails with a
syntax error due to a missing comma. As I mentioned previously,
please post the *exact* code you're running so typographic errors
in the message don't distract from the real problem. Those typos
prevent people from loading the code into their own database so
they can test it unless they correct the mistakes, and then they
can't be sure they're running the same thing you are.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-08-31 02:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-30 16:15 plpgsql question Postgres Admin <postgres@productivitymedia.com>
2005-08-30 16:50 ` Michael Fuhr <mike@fuhr.org>
2005-08-30 17:59 ` Postgres Admin <postgres@productivitymedia.com>
2005-08-31 02:32 ` Michael Fuhr <mike@fuhr.org>
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