[SQL] Order of columns in a table important in a stored procedure?

2005-02-13 Thread Marc SCHAEFER 
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

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

2005-02-13 Thread Tom Lane 
Marc SCHAEFER <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[SQL] XML

2005-02-13 Thread Theo Galanakis 
Title: XML





I need help in getting the xml contribution installed. I have installed the contrib package for Postgres 7.4.5 and would appreciate any guidance as to how I get this working?

Theo




__
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.

[SQL] Working with XML.

2005-02-13 Thread Theo Galanakis 
Title: Working with XML.





Hi Folks,


Is there a way to pass in an xml string into a stored proc and thenplace this into a temp table? 


I use to be able to do this in sql server, it was quite handy as I could call one stored proc to update multiple records, here is a sample in sql server:

select CoverTypeID, ItemSQ, SituationID, ItemDescription, CoverAmount 
From  OpenXML ( @XmlHandle, '/cover/covertype/item',1 ) 
    With ( CoverTypeID int '../@id', 
  ItemSQ int '@id', 
          SituationID int '@situationID',
  ItemDescription varchar(100) '@description',
          CoverAmount money '@amount' )


I have managed to get get pgxml_xpath working, however Im not sure how to access specific rows in an xml document. E.g below there are two records, how do I access the second record, the following returns both ,'//query/row/cola values being (284122,525887):

select 
pgxml_xpath(
'284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65525887493253{ts ''2005-02-14 16:13:18''}6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3'

,'//query/row/cola/text()','','')




__
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.