coalesce in plpgsql, and other style questions
От | Ross Boylan |
---|---|
Тема | coalesce in plpgsql, and other style questions |
Дата | |
Msg-id | 1339523201.5384.105.camel@corn.betterworld.us обсуждение исходный текст |
Ответы |
Re: coalesce in plpgsql, and other style questions
(Jeff Davis <pgsql@j-davis.com>)
Re: coalesce in plpgsql, and other style questions (Merlin Moncure <mmoncure@gmail.com>) |
Список | pgsql-novice |
I just wrote my first pl/pgsql function, and would appreciate any comments people have on it. I'll be writing a bunch of similar functions, with semantics "give me the id of the object if exists, otherwise create it and give me the id." My solution seems excessively procedural to me. I thought I could get the right semantics with something like select coalesce((select id from mytable where name='foo'), (insert into mytable ('name') values('foo') returning id)) but I could not get that to work in plgsql. Also, I wonder if calling a column 'name' is asking for trouble. The actual function is a little more complicated because the table has the possibility of canonical entries with other entries pointing to them, I use 'did' for database id to avoid confusion with various other message ids the system uses; it's for tracking emails. Here's the code. As I said, I'd love comments on any aspect of it. /* gom = get or make functions retrieve did if available, otherwise create record and return its did. */ create or replace function gom_hostids(IN hostname text, IN canonicalname text = NULL, OUT hostid bigint, OUT canonicalid bigint) language plpgsql as $$ DECLARE BEGIN select did, canonical into hostid, canonicalid from host where name = hostname; if FOUND then return; end if; if canonicalname is not NULL then select did into canonicalid from host where name = canonicalname; if not FOUND then insert into host (name) values(canonicalname) returning did into canonicalid; end if; end if; if hostname != canonical then insert into host (name, canonical) values(hostname, canonicalid) returning did into hostid; else hostid := canonicalid; return; END $$; Table definition: create table host ( did bigint primary key default (nextval('rb_id_seq')), ---database id, like oid name text, --IP address legal, usually enclosed in []. More commonly an internet domain. -- domain is a sql keyword and so I used host. canonical bigint references host (did) --if not null, preferred name for host --may refer to self ) inherits (RBObject); I'm using Posgresql 8.4. Thanks. Ross P.S. The recommended style of using "into", which puts the into near the start of selects but the end of inserts and deletes seems irregular to me. Also, I put the language clause of create function before the as clause because it seemed more natural to know that information before reading the body. I suppose if they are all the same language the language clause is just kind of noise, and that's why the examples have it at the end.
В списке pgsql-novice по дате отправления: