Problem with return type of function ???

[Denis BUCHER]

Hello,

I have a strange problem, because it worked in a fonction for a table,
and now I created the same (?) function for another table and it doesn't
work...

The function is accepted but at runtime I get :

ERREUR:  wrong record type supplied in RETURN NEXT
CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next

Does someone maybe knows what it could be ?

This is (a part of) my function :

> CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF rma.serial_number
>     AS $_$
> DECLARE
>    serialnumber ALIAS FOR $1;
>    row RECORD;
> BEGIN
>
> FOR row IN
> SELECT * FROM rma.serial_number WHERE sn=serialnumber
> LOOP
> RETURN NEXT row;
> END LOOP;
>
> END;
> $_$
>     LANGUAGE plpgsql STRICT;

Thanks a lot for any help !

Denis


--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Richard Huxton]

Denis BUCHER wrote:
>
> ERREUR:  wrong record type supplied in RETURN NEXT
> CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next
>
> Does someone maybe knows what it could be ?
>
> This is (a part of) my function :
>
>> CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF rma.serial_number

This is a different function. Function "hds_bw_find_sn_live" has the
wrong type for its "return next".

--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Denis BUCHER]

Richard Huxton a écrit :

>> ERREUR:  wrong record type supplied in RETURN NEXT
>> CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next
>>
>> Does someone maybe knows what it could be ?
>>
>> This is (a part of) my function :
>>
>>> CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF rma.serial_number
>
> This is a different function. Function "hds_bw_find_sn_live" has the
> wrong type for its "return next".

Hello,

Thanks a lot for your reply : the difference in name is just a mistake
in my email but in fact it is the same function, I just renamed it wrong
when  doing the "cleaning" before posting my email...

Denis

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Richard Huxton]

Denis BUCHER wrote:

> Richard Huxton a écrit :
>>> ERREUR:  wrong record type supplied in RETURN NEXT
>>> CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next
>>>
>>> Does someone maybe knows what it could be ?
>>>
>>> This is (a part of) my function :
>>>
>>>> CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF rma.serial_number
>> This is a different function. Function "hds_bw_find_sn_live" has the
>> wrong type for its "return next".
>
> Hello,
>
> Thanks a lot for your reply : the difference in name is just a mistake
> in my email but in fact it is the same function, I just renamed it wrong
> when  doing the "cleaning" before posting my email...

In that case - have you changed the definition of table
rma.serial_number since you defined the function?

--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Denis BUCHER]

Hello Richard,

Richard Huxton a écrit :

>>>> ERREUR:  wrong record type supplied in RETURN NEXT
>>>> CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next
>>>>
>>>> Does someone maybe knows what it could be ?
>>>>
>>>> This is (a part of) my function :
>>>>
>>>>> CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF rma.serial_number
>>> This is a different function. Function "hds_bw_find_sn_live" has the
>>> wrong type for its "return next".
>> Hello,
>>
>> Thanks a lot for your reply : the difference in name is just a mistake
>> in my email but in fact it is the same function, I just renamed it wrong
>> when  doing the "cleaning" before posting my email...
>
> In that case - have you changed the definition of table
> rma.serial_number since you defined the function?

No, I just do the test just after the "CREATE OR REPLACE FUNCTION".

I saw somwhere it could be the order of the fields ?

Denis

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Richard Huxton]

Denis BUCHER wrote:
> I saw somwhere it could be the order of the fields ?

Not if you are doing "SELECT * FROM".

Create an empty test database and a short script to create the table and
function, insert a couple of rows then call the function. If you wrap
the whole thing in BEGIN ... ROLLBACK we can change things until we see
the problem.

The other thing you could try is printing out row before returning it:
  RAISE NOTICE 'row = %', row;
  RETURN NEXT ROW;
It might be you've not got what you were expecting.

--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Denis BUCHER]

Hello Richard,

Richard Huxton a écrit :

>> I saw somwhere it could be the order of the fields ?
>
> Not if you are doing "SELECT * FROM".
>
> Create an empty test database and a short script to create the table and
> function, insert a couple of rows then call the function. If you wrap
> the whole thing in BEGIN ... ROLLBACK we can change things until we see
> the problem.
>
> The other thing you could try is printing out row before returning it:
>   RAISE NOTICE 'row = %', row;
>   RETURN NEXT ROW;
> It might be you've not got what you were expecting.

Thanks a lot, good idea...

But it looks good :

> SELECT * FROM rma.test ('19G256259');
> NOTICE:  row = (12066602,19G256259,170224,PN6405B,2009-09-22,"FORERUNNER 405 NOIR",2009-09-22,15090,14748)
> ERREUR:  wrong record type supplied in RETURN NEXT
> CONTEXTE : PL/pgSQL function "test" line 12 at return next
>
> \d rma.serial_number
>                                        Table « rma.serial_number »
>    Colonne   |         Type          |                           Modificateurs
> -------------+-----------------------+-------------------------------------------------------------------
>  sn_id       | bigint                | not null default nextval('rma.serial_number_sn_id_seq'::regclass)
>  sn          | character varying(30) |
>  no_client   | integer               |
>  no_art_bw   | character varying(11) |
>  sn_fc_date  | date                  |
>  desc_fr     | character varying(40) |
>  sn_cm_date  | date                  |
>  no_facture  | integer               |
>  no_commande | integer               |

Denis

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Richard Huxton]

Denis BUCHER wrote:
> Richard Huxton a écrit :
>> The other thing you could try is printing out row before returning it:
>>   RAISE NOTICE 'row = %', row;
>>   RETURN NEXT ROW;
>> It might be you've not got what you were expecting.
>
> Thanks a lot, good idea...
>
> But it looks good :

Hmm...

>> SELECT * FROM rma.test ('19G256259');
>> NOTICE:  row = (12066602,19G256259,170224,PN6405B,2009-09-22,"FORERUNNER 405 NOIR",2009-09-22,15090,14748)
>> ERREUR:  wrong record type supplied in RETURN NEXT
>> CONTEXTE : PL/pgSQL function "test" line 12 at return next
>>
>> \d rma.serial_number
>>                                        Table « rma.serial_number »
>>    Colonne   |         Type          |                           Modificateurs
>> -------------+-----------------------+-------------------------------------------------------------------
>>  sn_id       | bigint                | not null default nextval('rma.serial_number_sn_id_seq'::regclass)
>>  sn          | character varying(30) |
>>  no_client   | integer               |
>>  no_art_bw   | character varying(11) |
>>  sn_fc_date  | date                  |
>>  desc_fr     | character varying(40) |
>>  sn_cm_date  | date                  |
>>  no_facture  | integer               |
>>  no_commande | integer               |

I was wondering if maybe there was a bug to do with domains or complex
column types, but there's nothing out of the ordinary here.

OK - can you generate a test script with just CREATE TABLE, CREATE
FUNCTION, one INSERT and a function-call? I'll try and recreate it here.
Oh, and what version of PostgreSQL are we talking about?

--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Denis BUCHER]

Richard Huxton a écrit :

>>> The other thing you could try is printing out row before returning it:
>>>   RAISE NOTICE 'row = %', row;
>>>   RETURN NEXT ROW;
>>> It might be you've not got what you were expecting.
>> Thanks a lot, good idea...
>>
>> But it looks good :
>
> Hmm...
>
>>> SELECT * FROM rma.test ('19G256259');
>>> NOTICE:  row = (12066602,19G256259,170224,PN6405B,2009-09-22,"FORERUNNER 405 NOIR",2009-09-22,15090,14748)
>>> ERREUR:  wrong record type supplied in RETURN NEXT
>>> CONTEXTE : PL/pgSQL function "test" line 12 at return next
>>>
>>> \d rma.serial_number
>>>                                        Table « rma.serial_number »
>>>    Colonne   |         Type          |                           Modificateurs
>>> -------------+-----------------------+-------------------------------------------------------------------
>>>  sn_id       | bigint                | not null default nextval('rma.serial_number_sn_id_seq'::regclass)
>>>  sn          | character varying(30) |
>>>  no_client   | integer               |
>>>  no_art_bw   | character varying(11) |
>>>  sn_fc_date  | date                  |
>>>  desc_fr     | character varying(40) |
>>>  sn_cm_date  | date                  |
>>>  no_facture  | integer               |
>>>  no_commande | integer               |
>
> I was wondering if maybe there was a bug to do with domains or complex
> column types, but there's nothing out of the ordinary here.

Yes...

> OK - can you generate a test script with just CREATE TABLE, CREATE
> FUNCTION, one INSERT and a function-call? I'll try and recreate it here.
> Oh, and what version of PostgreSQL are we talking about?

> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.

OK I prepared what you asked and I tested it myself before sending.

And I think I've found the problem (but not the solution !) :


When I dump the FUNCTION, I get this :

> CREATE test(character varying) RETURNS SETOF serial_number

instead of this :

> CREATE test(character varying) RETURNS SETOF rma.serial_number

That seems to be some bug in Postgres ?

The problem is then clear, it doesn't take SETOF rma.serial_number but
SETOF public.serial_number

Do you see how we could solve this ? And do you think this is the problem ?

Thanks a lot again for all your help !

Denis

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Richard Huxton]

Denis BUCHER wrote:
>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.

OK - I'm not aware of any problems in that version. You're only one
revision from the latest 8.1.x series.

> OK I prepared what you asked and I tested it myself before sending.
>
> And I think I've found the problem (but not the solution !) :
>
>
> When I dump the FUNCTION, I get this :
>
>> CREATE test(character varying) RETURNS SETOF serial_number
>
> instead of this :
>
>> CREATE test(character varying) RETURNS SETOF rma.serial_number
>
> That seems to be some bug in Postgres ?

If you look earlier you should see a line that says something like: "set
search_path = rma, ..."

Makes it irritating to cut+paste sections of the dump, but it works just
fine.

--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql