Обсуждение: Problem with return type of function ???
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
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
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
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
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
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
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
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
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
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