Re: My First Function - Mailing list pgsql-sql

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.