Re: My First Function

http://postgresql.bteg.net/devel-corner/docs/postgres/plpgsql.html

>From: Gary Stainburn <gary.stainburn@ringways.co.uk>
>To: "omid omoomi" <oomoomi@hotmail.com>, gary.stainburn@ringways.co.uk, 
>pgsql-sql@postgresql.org
>Subject: Re: [SQL] My First Function
>Date: Wed, 4 Jul 2001 13:41:29 +0100
>
>Hi Omid,
>
>Thanks for this, I can more or less read it so I should be able to
>(eventually) write similar ones.
>
>I noticed you put "langualge 'sql'".  Presumaby this is because the whole
>thing is done as one select statement, therefore not needing 'plsql'.  What
>sort of extra stuff do you get from plsql? and can you suggest good places 
>to
>look at example plsql code?
>
>Gary.
>
>On Wednesday 04 July 2001  1:40 pm, omid omoomi wrote:
> > Hi,
> > Then you would need some more normalized tables. But anyway ...
> >
> > create function maxfunc(text,text)
> > returns int4 as '
> > select Max(PPERM) from permtable,holdertables
> > where PFID = $1 and PPID = HPID and HMID= $2  group by PPID; '
> > language 'sql' ;
> >
> > ...where $1,$2 are the arguments.
> >
> > This would give you the corresponding access level number.
> >
> > But If using PHP then you may have some thing like this:
> >
> > <?php
> > $conn = pg_pconnect("localhost","","","","DB");
> > $result = pg_Exec ($conn, " select Max(PPERM) from 
>permtable,holdertables
> > where PFID = $1 and PPID = HPID and HMID= $2  group by PPID; ");
> >
> > if (!$result) { $Acclevel = -1 ; }     /* if no record found
> > else { $Acclevel = pg_result($result,0,0);)  /* if found a record
> >
> > ?>
> >
> > I havn't test it my self, but sure it would work.
> >
> > good luck
> > Omid
> >
> >
> > From: Gary Stainburn <gary.stainburn@ringways.co.uk>
> >
> > >To: "omid omoomi" <oomoomi@hotmail.com>, gary.stainburn@ringways.co.uk,
> > >pgsql-sql@postgresql.org
> > >Subject: Re: [SQL] My First Function
> > >Date: Tue, 3 Jul 2001 17:08:06 +0100
> > >
> > >Okay, thanks for that.
> > >
> > >I'm getting far enough with my SQL to be able to read and understand 
>that
> > >one, but how do I put that into a plsql function and how do I call it.
> > >
> > >Lets asume I've changed the relationship so that there is only one 
>person
> > >per
> > >position, so only one integer should be returned.  How do I get that
> > >integer
> > >returned to my perl/php script, and how do I return -1 if the result of
> > > the query is empty?
> > >
> > >Gary
> > >
> > >On Sunday 01 July 2001  5:39 pm, omid omoomi wrote:
> > > > >I want to be able to pass a member ID and a function ID and return 
>the
> > > > >highest permission for the two.  The cut-down schema is:
> > > >
> > > > Hello ,
> > > > Perhaps this SQL works for you:
> > > >
> > > > select PPID,Max(PPERM)
> > > > from permtable
> > > > where
> > > > PFID = 'FUNCTION_ID'
> > > > and
> > > > PPID in ( select HPID from holdertable where HMID='MEMBER_ID' )
> > > > group by PPID;
> > > >
> > > > You will face some problems returning more than one value from this 
>SQL
> > >
> > >if
> > >
> > > > using it in a function.
> > > > Though,I believe if you are going to use a PHP script it can easilly 
>be
> > > > handled with it.
> > > >
> > > > Hope that helps
> > > > Omid Omoomi
> > >
> > 
> >_________________________________________________________________________
> > >
> > > > Get Your Private, Free E-mail from MSN Hotmail at
> > >
> > >http://www.hotmail.com.
> > >
> > >--
> > >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
> > >
> > >---------------------------(end of 
>broadcast)---------------------------
> > >TIP 4: Don't 'kill -9' the postmaster
> >
> > 
>_________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at 
>http://www.hotmail.com.
>
>--
>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

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.