Re: [SQL] FOR EACH STATEMENT trigger ?

2011-05-09 Thread Pavel Stehule
Hello it isn't bug. PostgreSQL doesn't support NEW and OLD tables like MSSQL does for statement triggers. Regards Pavel Stehule 2011/5/6 Frédéric BROUARD : > Hi there > > I am trying to get an example of SET BASED trigger logic with FOR EACH > STATEMENT, but I cannot find any example involving

[SQL] Trigger: update if record exists

2011-05-09 Thread Tarlika Elisabeth Schmitz
I wrote a trigger function to convert inserts into updates if the record exists already. - I am not using rules because the table is populated via COPY. - I am employing a two-stage process (PERFORM, then UPDATE) because the update trigger might decide not to update after all, and therefore FOUND

[SQL] Sorting Issue

2011-05-09 Thread Ozer, Pam
I have the following query Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId from VehicleTrimAbbreviated Where vehicleTrimAbbreviated like 'CX%' order by VehicleTrimAbbreviated asc Results: 532;"CX Hatchback" 536;"CXL Minivan" 3255;"CXL Premium Sedan" 537;"CXL Sedan" 538;"CXL

Re: [SQL] Sorting Issue

2011-05-09 Thread Emi Lu
I have the following query Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId from VehicleTrimAbbreviated Where vehicleTrimAbbreviated like 'CX%' order by VehicleTrimAbbreviated asc Results: 532;"CX Hatchback" 536;"CXL Minivan" 3255;"CXL Premium Sedan" 537;"CXL Sedan" 538;"CXL Sport

Re: [SQL] Sorting Issue

2011-05-09 Thread Ozer, Pam
That works. Why? -Original Message- From: Emi Lu [mailto:em...@encs.concordia.ca] Sent: Monday, May 09, 2011 12:38 PM To: Ozer, Pam Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Sorting Issue > I have the following query > > Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId > > f

Re: [SQL] Sorting Issue

2011-05-09 Thread Emi Lu
That works. Why? http://www.postgresql.org/docs/current/static/functions-string.html split_part(string text, delimiter text, field int) text Split string on delimiter and return the given field (counting from one) split_part('abc~@~def~@~ghi', '~@~', 2) def Emi -Original Message--

Re: [SQL] Sorting Issue

2011-05-09 Thread Ozer, Pam
Ok but why doesn't the other way work? I can't use the function in my query. It is dynamically created. -Original Message- From: Emi Lu [mailto:em...@encs.concordia.ca] Sent: Monday, May 09, 2011 12:52 PM To: Ozer, Pam Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Sorting Issue > Tha

Re: [SQL] Sorting Issue

2011-05-09 Thread Emi Lu
Hi Pam, >> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId >> from VehicleTrimAbbreviated >> Where vehicleTrimAbbreviated like 'CX%' >> order by >> >> split_part(VehicleTrimAbbreviated, ' ', 1) asc, >> split_part(VehicleTrimAbbreviated, ' ', 2) asc; This query works, right? Reason:

Re: [SQL] Sorting Issue

2011-05-09 Thread Samuel Gendler
On Mon, May 9, 2011 at 1:38 PM, Emi Lu wrote: > Hi Pam, > > > >> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId > >> from VehicleTrimAbbreviated > >> Where vehicleTrimAbbreviated like 'CX%' > >> order by > >> > >> split_part(VehicleTrimAbbreviated, ' ', 1) asc, > >> split_part(VehicleTri

Re: [SQL] Sorting Issue

2011-05-09 Thread Tom Lane
Samuel Gendler writes: > It's not at all clear why they are not coming out of the db in > alphabetically sorted order when the query includes "order by > VehicleTrimAbbreviated asc" Usually the thing to ask at this point is "what's the database's LC_COLLATE setting"? Non-C locales often have tr

Re: [SQL] Sorting Issue

2011-05-09 Thread Ozer, Pam
The collate setting is LC_COLLATE = 'English_United States.1252' -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, May 09, 2011 2:29 PM To: Samuel Gendler Cc: em...@encs.concordia.ca; Ozer, Pam; pgsql-sql@postgresql.org Subject: Re: [SQL] Sorting Issue Samue