Re: coalesce in plpgsql, and other style questions

From: Ross Boylan <ross(at)biostat(dot)ucsf(dot)edu>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: ross(at)biostat(dot)ucsf(dot)edu, pgsql-novice(at)postgresql(dot)org
Subject: Re: coalesce in plpgsql, and other style questions
Date: 2012-06-13 01:57:17
Message-ID: 1339552637.5384.164.camel@corn.betterworld.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Tue, 2012-06-12 at 18:17 -0700, Jeff Davis wrote:
> On Tue, 2012-06-12 at 17:42 -0700, Ross Boylan wrote:

> > > > 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
> > >
> > > Is canonical a proper variable here? It's not in the argument list, and
> > > it's not DECLAREd. Did you mean canonicalname?
> > canonical is a column name in the table. Perhaps canonical_did would be
> > more appropriate for it (and rename the output parameter
> > out_canonical_did from canonicalid).
>
> Does the function actually work like it is now?
No :) Thanks for your persistence. I made some changes after I tested
it. That last bit should be
if hostname != canonicalname then
insert into host (name, canonical) values(hostname, canonicalid)
returning did into hostid;
else
hostid := canonicalid;
end if;
return;

There was a missing end if too. I may rethink my indenting the "end if"
with the previous block, at least if I have an "else". I'm also not
crazy about how emacs is handling indentation.

Ross

> It looks like
> "canonical" is being used outside of the scope of any query, so I don't
> see how it comes from the table. Maybe I'm still confused.
>
> Regards,
> Jeff Davis
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Daniel Staal 2012-06-13 13:52:03 Re: coalesce in plpgsql, and other style questions
Previous Message Jeff Davis 2012-06-13 01:17:32 Re: coalesce in plpgsql, and other style questions