Re: coalesce in plpgsql, and other style questions

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
>