Re: IF...THEN...ELSE on INSERT

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Vincent AE Scott <pgsql-novice(at)codex(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: IF...THEN...ELSE on INSERT
Date: 2001-12-08 03:27:40
Message-ID: 1007782060.7485.16.camel@kant.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Sat, 2001-12-08 at 02:13, Vincent AE Scott wrote:
>
> Thanks for that, and yes it does help somewhat, but it appears somewhat
> cumbersome to me. It appears to me ( but i could be well out of whack )
> that the function is losely linked to the table. If i change the table
> definition ( like adding an extra column ) do i have to modify the
> function aswell?
>
> Is there anyway i can just modify the behaviour of an insert statement
> to transparently do this for me. That way at least the database clients
> dont have to implicitly know about this function/feature. Does that
> make sense?

Yep, that does make sense. What you are asking for is effectively an
'INSERT OR UPDATE <table> (<field list> ) VALUES( <value list> ) ...'
syntax.

This is being looked at in some areas ("CREATE OR REPLACE FUNCTION",
"CREATE OR REPLACE VIEW", ...) for 7.3, I believe. Whether or not there
will be an "INSERT OR UPDATE ..." statement included in there I am not
sure. I am pretty sure it has been proposed, and I know it would make
things easier for many programmers, myself included :-)

I think that it might be possible to write a function to do what you
want generically in 7.1 or later, but it would be complex. PL/PGSQL
will let you PERFORM a query you have constructed as a string, and you
can pass a row to a function, so you could (perhaps) do something very
complicated and unmaintainable there.

You might be better with a more complex language like PL/Perl for
something along those lines, or with a C function.

Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Francisco Reyes 2001-12-08 14:11:29 Re: Disk space problem...
Previous Message Aarni Ruuhimäki / Megative Tmi / KYMI.com 2001-12-08 01:45:17 Backup automation