Message-ID: <1108334609.420fd81180f5e@mail.airlinksystems.com>
Date: Sun, 13 Feb 2005 16:43:29 -0600
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?
References: <20050213170347.GA2658@defian.alphanet.ch>
In-Reply-To: <20050213170347.GA2658@defian.alphanet.ch>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.2.4

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.