Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function problem after alter table

Function problem after alter table

From
Sean Dooley
Date:
Hi,
I have a function as defined as so...

===========

CREATE OR REPLACE FUNCTION getitemdates(in_item_id integer) RETURNS
SETOF item_date
     AS $$
DECLARE
    resultset RECORD;
    old_path text;
BEGIN
    old_path := pg_catalog.current_setting('search_path');
    PERFORM pg_catalog.set_config('search_path', 'public, pg_temp', true);
    FOR RESULTSET IN
        SELECT * FROM item_date WHERE item_id = in_item_id
    LOOP
        RETURN NEXT resultset;
    END LOOP;

    PERFORM pg_catalog.set_config('search_path', old_path, true);

END;
$$
     LANGUAGE plpgsql STABLE SECURITY DEFINER;

===========


This works fine, returns all the matching data from item_date

However, if I alter the table item_date, for example

ALTER TABLE item_date ADD COLUMN foo text;

When I run the function now, I get the error

ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "getitemdates" line 10 at return next

It seems the item_date rowtype isn't being updated when the item_date
table is altered. If I then drop the added column, I still get the error
message. The procedure has completely broken.

Any ideas where I am going wrong?
Thanks,
Sean

Re: Function problem after alter table

From
Tom Lane
Date:
Sean Dooley <srd1@st-andrews.ac.uk> writes:
>     FOR RESULTSET IN
>         SELECT * FROM item_date WHERE item_id = in_item_id
>     LOOP
>         RETURN NEXT resultset;
>     END LOOP;

> This works fine, returns all the matching data from item_date
> However, if I alter the table item_date, for example
> ALTER TABLE item_date ADD COLUMN foo text;
> When I run the function now, I get the error
> ERROR:  wrong record type supplied in RETURN NEXT

This example works for me in PG 8.3.  In prior releases I think you'd
need to use FOR ... IN EXECUTE to force the SELECT to get re-planned
each time through.

            regards, tom lane

SQL Question

From
Joshua
Date:
Hello,

Please let me know if you can assist.... consider the following table
via the following sql statement:

SELECT ronum, jobnum, jobcode FROM slrojobs WHERE ronum = '56890' ORDER
BY jobnum

The above query retrieves the following results....

ronum        jobnum        jobcode
56890        1                     FLPQ
56890        2                     W


My question is this. I want to list the 'jobcodes' as follows for each
ronum in 1 field returned that is comma seperated.

FLPQ, W




Please let me know if anyone has a solution.
Thanks!!!!! :)

Re: SQL Question

From
"Vyacheslav Kalinin"
Date:
SELECT array_to_string(
    array(SELECT jobcode FROM slrojobs WHERE ronum = '56890' ORDER BY jobnum)
), ', ')

Re: SQL Question

From
"Vyacheslav Kalinin"
Date:
My fault, there was an excess parenthesis. Should be
SELECT array_to_string(
   array(SELECT jobcode FROM slrojobs WHERE ronum = '56890' ORDER BY jobnum)
, ', ')