pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: My First Function
6+ messages / 2 participants
[nested] [flat]
* Re: My First Function
@ 1970-01-01 00:00 omid omoomi <oomoomi@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: omid omoomi @ 1970-01-01 00:00 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; pgsql-sql
>
>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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: My First Function
@ 1970-01-01 00:00 omid omoomi <oomoomi@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: omid omoomi @ 1970-01-01 00:00 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: My First Function
@ 1970-01-01 00:00 omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: omid omoomi @ 1970-01-01 00:00 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* My First Function
@ 2001-07-01 15:30 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Gary Stainburn @ 2001-07-01 15:30 UTC (permalink / raw)
To: pgsql-sql
Hi all,
Background
To date I've been using perl and DBI/DBD:Pg. I've done everything except SQL
stuff in perl and done okay(ish).
I'm just about to teach myself PHP before starting a new project, and have in
front of me a problem. A number of perl subs that I've used in the past will
now need porting to PHP. Any new routines would need to to written in both.
Thus, I've decided to also learn plsql in an attempt reduce my workload. As
I work best from examples and tutorials than I do from manuals, I was
wondering if some kind sole would write my first function for me. (This task
is a little extreme, basically what I will need)
The function I want is to provide access control to functions I will be
writing on a web site for REVCOM, a UK Charity. The idea is that I have
members who may hold positions on the management committee. There is a
many-to-many relationship between positions and members. Access to functions
is dependant on the position held by a member. For example, the treasurer
can update the accounts, the secretary can view the accounts. If both
positions are held by the same person then the treasurer's rights should be
returned for that function. The result should be an integer where -1 = no
access, or a +ID gives increasing permissions.
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:
Members table holds member details
MID int4 (Primary key) member ID
Functions table holds function details
FID int4 (Primary key) function ID
Positions table Holds Comittee Position details
PID int4 (Primary key) Position ID
Holder table Relationship between members and positions
HMID int4 Position holding member's ID
HPID int4 Position ID
Permissions table Relationship between functions and committee positions
PFID int4 Function ID
PPID int4 Position ID
PPERM permission level
I understand that this is a biggie, but I thought if I could understand how
this worked, I could learn much from it.
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: My First Function
@ 2001-07-03 16:08 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Gary Stainburn @ 2001-07-03 16:08 UTC (permalink / raw)
To: omid omoomi <oomoomi@hotmail.com>; gary.stainburn@ringways.co.uk; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: My First Function
@ 2001-07-04 12:41 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Gary Stainburn @ 2001-07-04 12:41 UTC (permalink / raw)
To: omid omoomi <oomoomi@hotmail.com>; gary.stainburn@ringways.co.uk; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-07-04 12:41 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Re: My First Function omid omoomi <oomoomi@hotmail.com>
1970-01-01 00:00 Re: My First Function omid omoomi <oomoomi@hotmail.com>
2001-07-03 16:08 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
1970-01-01 00:00 Re: My First Function omid omoomi <oomoomi@hotmail.com>
2001-07-04 12:41 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-01 15:30 My First Function Gary Stainburn <gary.stainburn@ringways.co.uk>
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