RE: [SQL] SQL 'Case When...'
> Don't know if it's possible, but how do I make the following SQL > statments right? > > select attribute[1] > from table > case when attribute[1]='yes' > then select attribute[2] from table > else select attribute[3] from table; > > Basically, I want to compare attr[1], if true then select some fields > from table; if false then select others. Try this: select case attribute[1] when 'yes' then attribute[2] else attribute[3] end fromtable; -- Oliver Graf Triestram & Partner GmbH Kohlenstr. 55, 44795 Bochum, Germany Phone +49 234 94375-15 Fax +49 234 452206 eMail: [EMAIL PROTECTED] http://www.t-p.com
[SQL] Trouble with creating tempoprary tables in plpgsql
Hi I create some temporary tables in plpgsql function: "create temp table as select * ..." all works if I execute this function standalone but if this function is called by another function more then once temp tables can`t be create because ones exists I was trying bypass this first create temp tables and after insert to them data but error arised when I wrote "create temp table( some params );" or "drop table ..." Help me please How can I avoid a need to create static tables and how can I drop temporary tables If it is possible :-) Thank you -- "The Element" C o m p u t e r Information & Systems Company: http://element.anapa.org.ru
Re: [SQL] Problem with subquery in CHECK constraint.
Hiroshi Inoue wrote:
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> > Behalf Of Niall Smart
> >
> > CONSTRAINT TYPE_CD_OK CHECK (
> > EXISTS (SELECT 1 FROM XREF WHERE
> > XREF_GROUP = 'CUST_TYPE' AND
> > XREF_CD = TYPE_CD)
> > )
> >
> >
> > > There seems to be more serious problems.
> > > 1) The constraint is not only for the defined table but also
> > for referenced
> > > tables in the subquery.
> >
> > I don't understand what you mean -- the constraint only
> > constrains 1 column in one table...
>
> Doesn't the constraint mean that
> for any row in table CUST,there *always* exist some rows in
> the table XREF such that satisfies XREF_GROUP='CUST_TYPE'
> AND XREF_CD=TYPE_CD ?
>
> If all such rows are deleted from the table XREF,above condition
> isn't satisfied any longer. So isn't the constraint for the table XREF
> either ?
Ah, I see what you mean now. Well, thats an interesting point,
as I originally tried to define the constraint using the
foreign key syntax. Perhaps a better way to support this
functionality is to allow constants in the source columns
of a foreign key constraint, for example:
CONSTRAINT TYPE_CD_OK
FOREIGN KEY ('CUST_TYPE' AS XREF_GROUP, XREF_CD)
REFERENCES XREF
However I would submit that constraint clauses are
effectively before-insert validation triggers and that
it is up to the database designer to use more robust
methods when they wish to enforce referential integrity.
Note that it is possible to define a CHECK constraint using
a function, in which case it is impossible to determine if
the function is expressing a relational integrity constraint.
Niall
[SQL] Something I'd like to try...
Hi, I just noticed that postgres doesn't totally support column aliases on UPDATE statements, for example UPDATE EMPLOYEES SET OFFICE_PHONE = UU.OFFICE_PHONE, MOBILE_PHONE = UU.MOBILE_PHONE, OFFICE_CD = UU.OFFICE_CD, ABOUT_ME = UU.ABOUT_ME FROM UNCONFIRMED_UPDATES UU WHERE EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND UU.UPDATE_ID = 'HJhjaJ023J19KJAqp' It is not currently possible to alias EMPLOYEES so that the test can become E.EMPLOYEE_ID = UU.EMPLOYEE_ID Do the guru's think that this would be hard to add? Also, is it desirable? -- Niall Smart email: [EMAIL PROTECTED] phone: (087) 8052390
Re: [SQL] ORDER BY in definition of views
Niall Smart wrote: > Guys, > > Does anyone know if this is on the radar for 7.1? Yes, it is not. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[SQL] Client Logging
I sometimes need to be able to identify what client application is causing messages that are appering in the backend log file. To do this I want the client to identify itself in the log file. I can see 3 ways this could be implemented: 1) An identification string passed when a connection is established. This string would either be immediatly logged or included in the log entry for any subsequent messages for that connection. 2) A sql statement to print to the log file. 3) A function that takes a string argument and calls elog. #1 would be ideal but requires digging into the source code. #2 seams wrong headed. I can implement #3 locally myself. Before I go any farther, has something like this already been invented?
[SQL] to convert a statement ...
Hi, I would like to convert the following file: http://www.uwindsor.ca/library/leddy/people/art/pycreate.sql so it is acceptable by postgresql. It was written for MySQL. thank you for any help Benedict P. Barszcz
[SQL] list & check tables
I know it sounds simple but I'm having trouble finding commands to return all of the tables in a database ... and test if a certain table exists... I know you can use \dt etc but I want to have something I can call from php so it will return an array or some form I can use in the code. are there commands for this or will I have to do something fancy like keep another table just to track the tables?
