Postgres Pro
Downloads
Home   >   mailing lists

Thread: structure of query does not match error during RETURN QUERY.

structure of query does not match error during RETURN QUERY.

From
Michal Szymanski
Date:
Hi,
We call DB procedure that select rows with given ID it works as simple
SELECT but for future changes we implement as DB procedure (look below
for DB listing). Recently we modified columns in table
cerber.cerber_accoun and after this modification procedure does not
work anymore and it returns
42804: structure of query does not match
It is very strange because we return row of cerber_account in variable
defined as row of cerber_account.
We have tried to restart database but it does not help. Probably
information about old table structure is somewhere cached.

CREATE OR REPLACE FUNCTION cerber.cerber_account_select
(i_cerber_account_id bigint)
  RETURNS SETOF cerber.cerber_account AS
$BODY$
DECLARE
    v_result cerber.cerber_account%ROWTYPE;
BEGIN
    RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
cerber_account_id=i_cerber_account_id;
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


Michal Szymankis
http://blog.szymanskich.net

Re: structure of query does not match error during RETURN QUERY.

From
Michal Szymanski
Date:
We use Postgres 8.3.7 on Linux

Re: structure of query does not match error during RETURN QUERY.

From
Gevik Babakhani
Date:
How do you call this function from your code?

Does it work when you call it from PGAdmin?

select  * from cerber.cerber_account_select (1);

Michal Szymanski wrote:
> Hi,
> We call DB procedure that select rows with given ID it works as simple
> SELECT but for future changes we implement as DB procedure (look below
> for DB listing). Recently we modified columns in table
> cerber.cerber_accoun and after this modification procedure does not
> work anymore and it returns
> 42804: structure of query does not match
> It is very strange because we return row of cerber_account in variable
> defined as row of cerber_account.
> We have tried to restart database but it does not help. Probably
> information about old table structure is somewhere cached.
>
> CREATE OR REPLACE FUNCTION cerber.cerber_account_select
> (i_cerber_account_id bigint)
>   RETURNS SETOF cerber.cerber_account AS
> $BODY$
> DECLARE
>     v_result cerber.cerber_account%ROWTYPE;
> BEGIN
>     RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
> cerber_account_id=i_cerber_account_id;
>     RETURN;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100
>   ROWS 1000;
>
>
> Michal Szymankis
> http://blog.szymanskich.net
>
>

Re: structure of query does not match error during RETURN QUERY.

From
Gevik Babakhani
Date:
I see that now... I guess this is bug. please see:
http://archives.postgresql.org//pgsql-bugs/2008-11/msg00172.php

If it is possible for you, dump and restore the database.

I hope this helps.


Michal Szymanski wrote:
> We have tried to call it from PHP and from pgadmin the result is
> always the same.
> This is result from psql
>
> crm_test=# select  * from cerber.cerber_account_select (1);
> WARNING:  :ERROR:CERBER:cerber_account_select: Blad typu other w
> trakcie probu p                                            obrania
> danych. Kod bledu = [42804: structure of query does not match function
> r                                            esult type]
>  cerber_account_id | user_name | password | status | last_login_date |
> creation_                                            date |
> modification_date | delete_date | id_sys_module | id_domain
> -------------------+-----------+----------+--------+-----------------+----------
      
> -----+-------------------+-------------+---------------+-----------
> (0 rows)
>
>
> Gevik Babakhani wrote:
>> How do you call this function from your code?
>>
>> Does it work when you call it from PGAdmin?
>>
>> select  * from cerber.cerber_account_select (1);
>>
>> Michal Szymanski wrote:
>>> Hi,
>>> We call DB procedure that select rows with given ID it works as simple
>>> SELECT but for future changes we implement as DB procedure (look below
>>> for DB listing). Recently we modified columns in table
>>> cerber.cerber_accoun and after this modification procedure does not
>>> work anymore and it returns
>>> 42804: structure of query does not match
>>> It is very strange because we return row of cerber_account in variable
>>> defined as row of cerber_account.
>>> We have tried to restart database but it does not help. Probably
>>> information about old table structure is somewhere cached.
>>>
>>> CREATE OR REPLACE FUNCTION cerber.cerber_account_select
>>> (i_cerber_account_id bigint)
>>>   RETURNS SETOF cerber.cerber_account AS
>>> $BODY$
>>> DECLARE
>>>     v_result cerber.cerber_account%ROWTYPE;
>>> BEGIN
>>>     RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
>>> cerber_account_id=i_cerber_account_id;
>>>     RETURN;
>>> END;
>>> $BODY$
>>>   LANGUAGE 'plpgsql' VOLATILE
>>>   COST 100
>>>   ROWS 1000;
>>>
>>>
>>> Michal Szymankis
>>> http://blog.szymanskich.net
>>>
>>>
>> ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.0.238 / Virus Database: 270.12.21/2103 - Release Date: 05/07/09 18:05:00
>>
>>
>

Re: structure of query does not match error during RETURN QUERY.

From
Michal Szymanski
Date:
We have tried to call it from PHP and from pgadmin the result is always the same.
This is result from psql

crm_test=# select  * from cerber.cerber_account_select (1);
WARNING:  :ERROR:CERBER:cerber_account_select: Blad typu other w trakcie probu p                                            obrania danych. Kod bledu = [42804: structure of query does not match function r                                            esult type]
 cerber_account_id | user_name | password | status | last_login_date | creation_                                            date | modification_date | delete_date | id_sys_module | id_domain
-------------------+-----------+----------+--------+-----------------+----------                                            -----+-------------------+-------------+---------------+-----------
(0 rows)


Gevik Babakhani wrote:
How do you call this function from your code?

Does it work when you call it from PGAdmin?

select  * from cerber.cerber_account_select (1);

Michal Szymanski wrote:
Hi,
We call DB procedure that select rows with given ID it works as simple
SELECT but for future changes we implement as DB procedure (look below
for DB listing). Recently we modified columns in table
cerber.cerber_accoun and after this modification procedure does not
work anymore and it returns
42804: structure of query does not match
It is very strange because we return row of cerber_account in variable
defined as row of cerber_account.
We have tried to restart database but it does not help. Probably
information about old table structure is somewhere cached.

CREATE OR REPLACE FUNCTION cerber.cerber_account_select
(i_cerber_account_id bigint)
  RETURNS SETOF cerber.cerber_account AS
$BODY$
DECLARE
    v_result cerber.cerber_account%ROWTYPE;
BEGIN
    RETURN QUERY SELECT * FROM cerber.cerber_account WHERE
cerber_account_id=i_cerber_account_id;
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


Michal Szymankis
http://blog.szymanskich.net

  


No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.0.238 / Virus Database: 270.12.21/2103 - Release Date: 05/07/09 18:05:00