Re: My First Function

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