pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder of columns in a table important in a stored procedure?
5+ messages / 5 participants
[nested] [flat]
* Order of columns in a table important in a stored procedure?
@ 2005-02-13 17:03 Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
2005-02-13 17:49 ` Re: Order of columns in a table important in a stored procedure? Tom Lane <tgl@sss.pgh.pa.us>
2005-02-13 22:43 ` Re: Order of columns in a table important in a stored procedure? Richard Gintz <dgintz@airlinksystems.com>
0 siblings, 2 replies; 5+ messages in thread
From: Marc SCHAEFER @ 2005-02-13 17:03 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order of columns in a table important in a stored procedure?
2005-02-13 17:03 Order of columns in a table important in a stored procedure? Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
@ 2005-02-13 17:49 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-02-13 17:49 UTC (permalink / raw)
To: Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>; +Cc: pgsql-sql
Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch> writes:
> 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.
It doesn't do it on the basis of column names.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order of columns in a table important in a stored procedure?
2005-02-13 17:03 Order of columns in a table important in a stored procedure? Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
@ 2005-02-13 22:43 ` Richard Gintz <dgintz@airlinksystems.com>
2005-02-16 20:51 ` Re: Order of columns in a table important in a stored procedure? Geoffrey <esoteric@3times25.net>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Gintz @ 2005-02-13 22:43 UTC (permalink / raw)
To: Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order of columns in a table important in a stored procedure?
2005-02-13 17:03 Order of columns in a table important in a stored procedure? Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
2005-02-13 22:43 ` Re: Order of columns in a table important in a stored procedure? Richard Gintz <dgintz@airlinksystems.com>
@ 2005-02-16 20:51 ` Geoffrey <esoteric@3times25.net>
2005-02-16 21:27 ` Re: Order of columns in a table important in a stored procedure? PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 5+ messages in thread
From: Geoffrey @ 2005-02-16 20:51 UTC (permalink / raw)
To: pgsql-sql
Richard Gintz wrote:
> Pardon me ya'll, but can you tell me what a "saisies" is?
More than one saisy???
--
Until later, Geoffrey
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order of columns in a table important in a stored procedure?
2005-02-13 17:03 Order of columns in a table important in a stored procedure? Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
2005-02-13 22:43 ` Re: Order of columns in a table important in a stored procedure? Richard Gintz <dgintz@airlinksystems.com>
2005-02-16 20:51 ` Re: Order of columns in a table important in a stored procedure? Geoffrey <esoteric@3times25.net>
@ 2005-02-16 21:27 ` PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 5+ messages in thread
From: PFC @ 2005-02-16 21:27 UTC (permalink / raw)
To: Geoffrey <esoteric@3times25.net>; pgsql-sql
French verb "saisir" : here, to enter data in a system by typing it.
noun "saisie" : the action of doing so.
It has other meanings :
"Saisir" :
- (commonly) to grab or get hold of something swiftly
- (sometimes) to understand something
- (lawspeak) that is also what the Oracle layers do to your house when
they find out about your benchmark publications. More in the "grab" sense.
On Wed, 16 Feb 2005 15:51:00 -0500, Geoffrey <esoteric@3times25.net> wrote:
> Richard Gintz wrote:
>> Pardon me ya'll, but can you tell me what a "saisies" is?
>
> More than one saisy???
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-02-16 21:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-13 17:03 Order of columns in a table important in a stored procedure? Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch>
2005-02-13 17:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-13 22:43 ` Richard Gintz <dgintz@airlinksystems.com>
2005-02-16 20:51 ` Geoffrey <esoteric@3times25.net>
2005-02-16 21:27 ` PFC <lists@boutiquenumerique.com>
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