Обсуждение: 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;
Re: 42804: structure of query does not match error where using RETURN QUERY
От
Michal Szymanski
Дата:
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>: > 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) > =C2=A0RETURNS SETOF cerber.cerber_account AS > $BODY$ > DECLARE > =C2=A0 =C2=A0 =C2=A0 =C2=A0v_result cerber.cerber_account%ROWTYPE; > BEGIN > =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURN QUERY SELECT * FROM cerber.cerber_accou= nt WHERE > cerber_account_id=3Di_cerber_account_id; > =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURN; > END; > $BODY$ > =C2=A0LANGUAGE 'plpgsql' VOLATILE > =C2=A0COST 100 > =C2=A0ROWS 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
Re: 42804: structure of query does not match error where using RETURN QUERY
От
Michal Szymanski
Дата:
> you have to do vacuum full =A0cerber.cerber_account Yes I've made vacuum full - result was the same. Regards Michal Szymanski
Re: 42804: structure of query does not match error where using RETURN QUERY
От
Michal Szymanski
Дата:
> Exactly what modifications did you make? We have added few new columns and we delete one column. Michal Szymanski http://blog.szymanskich.net
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
Re: Re: 42804: structure of query does not match error where using RETURN QUERY
От
Bruce Momjian
Дата:
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