From: Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
To: pgsql-sql@postgresql.org
Subject: Order of columns in a table important in a stored procedure?
Date: Sun, 13 Feb 2005 18:03:47 +0100
Message-ID: <20050213170347.GA2658@defian.alphanet.ch> (raw)
Hi,
I use a FOR one_row IN loop where one_row is of type saisies%ROWTYPE.
The loop does a SELECT on a table, bennes, changing a few values.
The idea is that the function should return some bennes rows, but
with additional information, which makes the returned rows
a saisies table-like row set.
I have however noticed that if the SELECT is *NOT* in the correct order
for the table saisies, funny errors happen (such as type constraints --
obviously columns are mixed).
What I do not understand is that I use AS in order to name the columns,
I would think PostgreSQL could get the column names paired.
This is annoying since it means that any change to the data structure,
such as adding columns may make my functions non working.
This is however an old version of PSQL (7.1 I think).
For reference:
CREATE OR REPLACE FUNCTION f_fa_montre(VOID)
RETURNS SETOF saisies
AS '
DECLARE
one_row saisies%ROWTYPE;
BEGIN
FOR one_row IN
SELECT NULL as idsaisie,
b.no_client AS num_client,
b.lieu_entreposage_b5 AS chantier,
DATE_TRUNC(\'month\', CURRENT_DATE) AS dates,
\'0\' AS num_bon,
NULL AS num_art
FROM bennes b
WHERE (type_fact_p = b.type_fact)
LOOP
-- here I do some changes to the one_row, BTW
RETURN NEXT one_row;
END LOOP;
RETURN;
END;'
LANGUAGE 'plpgsql';
The issue: if I exchange num_bon and dates above the query fails.
Thank you for any idea.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alphanet-postgresql-sql@alphanet.ch
Subject: Re: Order of columns in a table important in a stored procedure?
In-Reply-To: <20050213170347.GA2658@defian.alphanet.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox