Re: Order of columns in a table important in a stored procedure?

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(at)AirlinkSystems(dot)com

Quoting Marc SCHAEFER <alphanet-postgresql-sql(at)alphanet(dot)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.