pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

pl/pgsql problem with return types
3+ messages / 2 participants
[nested] [flat]

* pl/pgsql problem with return types
@ 2005-03-11 10:54 Juris Zeltins <juris.zeltins@gmail.com>
  2005-03-11 12:46 ` Re: pl/pgsql problem with return types John DeSoi <desoi@pgedit.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Juris Zeltins @ 2005-03-11 10:54 UTC (permalink / raw)
  To: pgsql-sql

Hello!

i have problem with pl/pgsql function;

====================================================
===
ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "tests" line 6 at return next
===

-- Function: tests(int8)
-- DROP FUNCTION tests(int8);

CREATE OR REPLACE FUNCTION tests(int8)
  RETURNS SETOF pages AS
$BODY$DECLARE
    P    RECORD;
BEGIN
    FOR P IN select pageid from pages
    LOOP
        RETURN NEXT P;
    END LOOP;
    RETURN;
END;$BODY$
  LANGUAGE 'plpgsql' STABLE;
ALTER FUNCTION tests(int8) OWNER TO diglat_web;

====================================================
-- Table: pages
-- DROP TABLE pages;

CREATE TABLE pages
(
  pageid int8 NOT NULL,
  ppageid int8 NOT NULL DEFAULT 0,
  name varchar(100),
  status int4 DEFAULT 0,
  CONSTRAINT pages_pkey PRIMARY KEY (pageid),
  CONSTRAINT pages_in_pages_fkey FOREIGN KEY (ppageid) REFERENCES pages 
(pageid) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT pages_uniq UNIQUE (pageid, ppageid)
)
WITH OIDS;
====================================================

Actualy function is correct... and the same code run successfully on 
other table.. there is the problem ?

Thanks




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: pl/pgsql problem with return types
  2005-03-11 10:54 pl/pgsql problem with return types Juris Zeltins <juris.zeltins@gmail.com>
@ 2005-03-11 12:46 ` John DeSoi <desoi@pgedit.com>
  2005-03-11 12:50   ` Re: pl/pgsql problem with return types Juris Zeltins <juris.zeltins@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: John DeSoi @ 2005-03-11 12:46 UTC (permalink / raw)
  To: Juris Zeltins <juris.zeltins@gmail.com>; +Cc: pgsql-sql


On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote:

>    FOR P IN select pageid from pages
>

This way you are only getting the pageid column. I think what you want 
is

FOR P in select * from pages

so that P contains the complete pages record.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: pl/pgsql problem with return types
  2005-03-11 10:54 pl/pgsql problem with return types Juris Zeltins <juris.zeltins@gmail.com>
  2005-03-11 12:46 ` Re: pl/pgsql problem with return types John DeSoi <desoi@pgedit.com>
@ 2005-03-11 12:50   ` Juris Zeltins <juris.zeltins@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Juris Zeltins @ 2005-03-11 12:50 UTC (permalink / raw)
  To: John DeSoi <desoi@pgedit.com>; +Cc: pgsql-sql

Yep.. i have solved this problem by specifying
the correct return type and variable type (should be the same)

but as said, in some cases pl_exec executes with type conversion.
as real example - i have :

return type = SETOF new type  "category_node(catid, pcatid)"
variable = R, SR -> RECORD

and

FOR R IN select * from ...
LOOP
   RETURN NEXT R;  /* add  RECORD to SETOF_of_CATEGORY_NODE */
....

and works...
the problem seems is teh same - on altered tables there is some porblem 
with this :)

Actualy - return type & variable in "RETURN NEXT" must be the same type.

// Solved

John DeSoi wrote:

>
> On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote:
>
>>    FOR P IN select pageid from pages
>>
>
> This way you are only getting the pageid column. I think what you want is
>
> FOR P in select * from pages
>
> so that P contains the complete pages record.
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-03-11 12:50 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-11 10:54 pl/pgsql problem with return types Juris Zeltins <juris.zeltins@gmail.com>
2005-03-11 12:46 ` John DeSoi <desoi@pgedit.com>
2005-03-11 12:50   ` Juris Zeltins <juris.zeltins@gmail.com>

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