Re: [SQL] problem using regexp_replace
On 2010-01-11, [email protected] wrote: > CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[]) > returns varchar > as > $$ > select case > $1[1] when 'Action_1' then > (select descripcion from load_by_cod($1[2])) > >when 'Action_2' then (select descripcion from pay_by_view($1[2]) > > else 'FALSE' > end; > $$ language sql; > Anybody has a hint? you are missing a ) -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem using regexp_replace
> On 2010-01-11, [email protected] wrote: > >> CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[]) >> returns varchar >> as >> $$ >> select case >> $1[1] when 'Action_1' then >> (select descripcion from load_by_cod($1[2])) >> >>when 'Action_2' then (select descripcion from pay_by_view($1[2]) >> >> else 'FALSE' >> end; >> $$ language sql; > >> Anybody has a hint? > > you are missing a ) > Oh, thats a copy-paste problem, sory about that. I forgot to mention, this is a 8.3 running on linux. Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] CHECK constraint removing brackets
Hi, I notice this had been raised as a bug (and subsequently over-ruled) so I'm asking how I can achieve the following business rule. I have an order table which has an invoice_id column that links to an invoice table (an order can only have 1 invoice, but one invoice can have multiple orders.) An order can have either an unconfirmed state, or any other state after it's been confirmed. If an order has the state unconfirmed, the invoice_id column must be null, as an invoice won't have been created yet. If an order has any other state except unconfirmed, the invoice_id must not be null. With the above in mind, I decided on the following check to enforce this: (state = 'Unconfirmed'::client.order_state AND invoice_id = NULL) OR (state != 'Unconfirmed'::client.order_state AND invoice_id != NULL) However PostgreSQL (8.4.2) converts this to the following: state = 'Unconfirmed'::client.order_state AND invoice_id = NULL::integer OR state <> 'Unconfirmed'::client.order_state AND invoice_id <> NULL::integer This allows both an order state of "unconfirmed" and a non-null invoice_id, and an order state of "confirmed" and a NULL invoice_id. How can I achieve the above? Thanks, Andy -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint removing brackets
On Mon, Jan 11, 2010 at 12:49 PM, Andy Shellam wrote: > With the above in mind, I decided on the following check to enforce this: > > (state = 'Unconfirmed'::client.order_state AND invoice_id = NULL) OR (state > != 'Unconfirmed'::client.order_state AND invoice_id != NULL) Nothing can = null. and invoice_id IS NULL is the proper nomenclature. Also, something <> NULL makes no sense, because we don't know what NULL is, so that becomes something IS NOT NULL Also != is not proper SQL, although many dbs understand it, <> is the proper way to write NOT EQUAL TO. > However PostgreSQL (8.4.2) converts this to the following: > > state = 'Unconfirmed'::client.order_state AND invoice_id = NULL::integer OR > state <> 'Unconfirmed'::client.order_state AND invoice_id <> NULL::integer ANDs have priority of ORs so the removal of the parenthesis makes no great change here. also, SQL standard is <> not !=. I'm guessing the real problems here are your NULL handling. See if changing it to IS NULL / IS NOT NULL gets you what you want. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint removing brackets
Andy Shellam writes: > With the above in mind, I decided on the following check to enforce this: > (state = 'Unconfirmed'::client.order_state AND invoice_id = NULL) OR (state > != 'Unconfirmed'::client.order_state AND invoice_id != NULL) > However PostgreSQL (8.4.2) converts this to the following: > state = 'Unconfirmed'::client.order_state AND invoice_id = NULL::integer OR > state <> 'Unconfirmed'::client.order_state AND invoice_id <> NULL::integer There is no "conversion" there, that means exactly the same thing. (AND binds tighter than OR.) I think your real problem is that you're trying to use "= NULL" and "!= NULL" where you should say IS NULL or IS NOT NULL. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint removing brackets
Hi Tom and Scott, > > I think your real problem is that you're trying to use "= NULL" and > "!= NULL" where you should say IS NULL or IS NOT NULL. Argh such a school-boy error! This is the first bit of database programming I've done for about 2 months, and I hadn't switched my C++ brain off. I know about the <> and !=, for some reason != has always made better sense to me to read, so I tend to write it that way. Cheers, Andy -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint removing brackets
Andy Shellam writes: > I know about the <> and !=, for some reason != has always made better sense > to me to read, so I tend to write it that way. Yeah, a lot of people prefer != ... that's why we provide it as an alias for <>. There's no functional difference. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
