From: Richard Gintz <dgintz@airlinksystems.com>
To: Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: Order of columns in a table important in a stored procedure?
Date: Sun, 13 Feb 2005 16:43:29 -0600
Message-ID: <1108334609.420fd81180f5e@mail.airlinksystems.com> (raw)
In-Reply-To: <20050213170347.GA2658@defian.alphanet.ch>
References: <20050213170347.GA2658@defian.alphanet.ch>
Pardon me ya'll, but can you tell me what a "saisies" is?
Thanks,
Dick
--
Richard Gintz
Airlink Systems
7600 Burnet Rd.
Suite 515
Austin, TX 78757
PH: 512-231-1240 x108
FX: 512-231-9884
EM: dgintz@AirlinkSystems.com
Quoting Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>:
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
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: dgintz@airlinksystems.com, alphanet-postgresql-sql@alphanet.ch
Subject: Re: Order of columns in a table important in a stored procedure?
In-Reply-To: <1108334609.420fd81180f5e@mail.airlinksystems.com>
* 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