pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: pl/pgsql - code review + question
Date: Wed, 18 Jul 2001 16:24:10 +0100
Message-ID: <0107181624100B.11978@gary.ringways.co.uk> (raw)
In-Reply-To: <01071815102309.11978@gary.ringways.co.uk>
References: <01071815102309.11978@gary.ringways.co.uk>

Okay, I've been hit round the back of the head, and I realised that the 
postgresql functions (inc subtring) are available in pl/pgsql, so that's my 
problem solved.

I've written the getmid function as below, which is basically the same as the 
getunitno I included in my first post.

My problem now is that when I include the code to handle the record not being 
there, from the pgsql chapter (section 23.2.3.3) I get the following errors 
based of the function below.  Can anyone explain why the concat of the string 
is failing. If I simply "raise exception ''member not found''" all works fine.

__BEGIN__ (screen output)
[revcom@curly revcom]$ psql -f t
DROP
CREATE
[revcom@curly revcom]$ psql -c "select getmid('NE/011-06');"
NOTICE:  plpgsql: ERROR during compile of getmid near line 15
ERROR:  parse error at or near "|"
[revcom@curly revcom]$
__END__
__BEGIN__ (~/t which contains the function def)
drop function getmid(varchar);
CREATE FUNCTION getmid(varchar) RETURNS int4  AS '
DECLARE
     unitno ALIAS FOR $1;
     teamno varchar;
     munit int4;
     results RECORD;
BEGIN
    teamno := substring(unitno from 1 for 6);
    munit := substring(unitno from 8);
    select into results m.mid as mid
        from teams t, members m
        where t.tid = m.mteam and 
              t.tnumber = ''teamno'' and
              m.mnumber = munit;
    if not found then
      raise exception ''Member '' || unitno || '' not found'';
      return 0;
    end if;
    return results.mid;
END;
' LANGUAGE 'plpgsql';
__END__

Gary
On Wednesday 18 July 2001  3:10 pm, Gary Stainburn wrote:
> Hi all, I've just written my first pl/pgsql function (code included below
> for you to pull apart).
>
> It takes an int4 mid (e.g. 15) and then using a select pulls out the team
> number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit
> number NE/012-02.
>
> I now want to write the reverse function, where I can enter 'NE/012-02' and
> get back the mid 15.  The bit I'm stuck on is now I split the team part
> from the member part so that I can build the select statement.
>
> TIA Gary
>
> __BEGIN__
> CREATE FUNCTION getunitno(int4) RETURNS varchar  AS '
> DECLARE
>      mid ALIAS FOR $1;
>      results RECORD;
> BEGIN
>     select into results t.tnumber as tnumber, m.mnumber as mnumber
>         from teams t, members m
>         where t.tid = m.mteam and m.mid = mid;
>     if results.mnumber < 10 then
>       return results.tnumber || ''-0'' || results.mnumber;
>     else
>       return results.tnumber || ''-'' || results.mnumber;
>     end if;
> END;
> ' LANGUAGE 'plpgsql';
> __END__

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     



view thread (14+ messages)  latest in thread

Message-ID: <0107181624100B.11978@gary.ringways.co.uk>
Permalink:  ../0107181624100B.11978@gary.ringways.co.uk/
Also on:    postgresql.org/message-id/0107181624100B.11978@gary.ringways.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gary.stainburn@ringways.co.uk
  Subject: Re: pl/pgsql - code review + question
  In-Reply-To: <0107181624100B.11978@gary.ringways.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox