42804: structure of query does not match error where using RETURN QUERY
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;
We use Postgres 8.3.7 on Linux
Hello
you have to do vacuum full cerber.cerber_account
regards
Pavel Stehule
2009/5/8 Michal Szymanski <mich20061@gmail.com>:
Show quoted text
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;--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Michal Szymanski <mich20061@gmail.com> writes:
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
Exactly what modifications did you make?
regards, tom lane
Exactly what modifications did you make?
We have added few new columns and we delete one column.
Michal Szymanski
http://blog.szymanskich.net
you have to do vacuum full cerber.cerber_account
Yes I've made vacuum full - result was the same.
Regards
Michal Szymanski
Michal Szymanski <mich20061@gmail.com> writes:
Exactly what modifications did you make?
We have added few new columns and we delete one column.
[ experiments... ] Hmm, looks like RETURN QUERY isn't too bright about
dropped columns in the function result type. I'm not too sure about how
hard this is to fix, but in the meantime you will need to dump and
recreate/reload that particular table in order to get it to work.
regards, tom lane
Tom Lane wrote:
Michal Szymanski <mich20061@gmail.com> writes:
Exactly what modifications did you make?
We have added few new columns and we delete one column.
[ experiments... ] Hmm, looks like RETURN QUERY isn't too bright about
dropped columns in the function result type. I'm not too sure about how
hard this is to fix, but in the meantime you will need to dump and
recreate/reload that particular table in order to get it to work.
Is this a TODO?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
[ experiments... ] Hmm, looks like RETURN QUERY isn't too bright about
dropped columns in the function result type. I'm not too sure about how
hard this is to fix, but in the meantime you will need to dump and
recreate/reload that particular table in order to get it to work.
Is this a TODO?
It's already there.
regards, tom lane