pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStored procedures and "pseudo" fields..
7+ messages / 5 participants
[nested] [flat]
* Stored procedures and "pseudo" fields..
@ 2004-07-20 10:43 Lars Erik Thorsplass <thorsplass@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Lars Erik Thorsplass @ 2004-07-20 10:43 UTC (permalink / raw)
To: pgsql-general@postgresql.org
I have recently ventured into the exciting world of stored procedures,
but I have now become lost.
Background:
Am currently working on access control in a web application. My goal
is to process access control on the SQL level. This way if a row is in
the result set, you have access to it, if not, you dont.
Problem:
My stored procedure "acl_check()" takes two integers as parameters.
Param1 is the object id to check acl on, Param 2 is the object id of
the user currently using the system. The procedure returns a positive
numer (1 or 3 ) if you have some kind of access to the object. As one
might understand I want the returned value from the acl_check()
procedure to be a part of the result set.
Kinda like this:
SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
WHERE mode > 0;
This gives me a: ERROR: column "mode" does not exist
If I remove the "mode > 0" logic, I get a resultset with mode in it as
expected. Why cant I do logic tests with the mode "field" ?
I tried a diffrent approach with a diffrent error:
SELECT * FROM objects, acl_check( objects.obid, 32 ) as mode WHERE mode > 0;
This gives me a: ERROR: function expression in FROM may not refer to
other relations of same query level.
Here objecs.obid is unknown i suppose, but if I enter "10" as the
first param using the mode "field" in a logic statement works.
I would appreciate any hints to a workaround which would enable me to
accomplish my scenario.
Best regards,
L.E.Thorsplass
^ permalink raw reply [nested|flat] 7+ messages in thread
* Stored procedures and "pseudo" fields..
@ 2004-07-20 13:34 Lars Erik Thorsplass <thorsplass@gmail.com>
parent: Lars Erik Thorsplass <thorsplass@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Lars Erik Thorsplass @ 2004-07-20 13:34 UTC (permalink / raw)
To: pgsql-sql
I also posted this to the general list, which might not have been a
suitable forum.
--
I have recently ventured into the exciting world of stored procedures,
but I have now become lost.
Background:
Am currently working on access control in a web application. My goal
is to process access control on the SQL level. This way if a row is in
the result set, you have access to it, if not, you don't.
Problem:
My stored procedure "acl_check()" takes two integers as parameters.
Param1 is the object id to check acl on, Param 2 is the object id of
the user currently using the system. The procedure returns a positive
number (1 or 3 ) if you have some kind of access to the object. As one
might understand I want the returned value from the acl_check()
procedure to be a part of the result set.
Kinda like this:
SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
WHERE mode > 0;
This gives me a: ERROR: column "mode" does not exist
If I remove the "mode > 0" logic, I get a result set with mode in it as
expected. Why cant I do logic tests with the mode "field" ?
I tried a different approach with a different error:
SELECT * FROM objects, acl_check( objects.obid, 32 ) as mode WHERE mode > 0;
This gives me a: ERROR: function expression in FROM may not refer to
other relations of same query level.
Here objecs.obid is unknown i suppose, but if I enter "10" as the
first param using the mode "field" in a logic statement works.
I would appreciate any hints to a workaround which would enable me to
accomplish my scenario.
Best regards,
L.E.Thorsplass
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedures and "pseudo" fields..
@ 2004-07-20 13:57 Tomasz Myrta <jasiek@klaster.net>
parent: Lars Erik Thorsplass <thorsplass@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tomasz Myrta @ 2004-07-20 13:57 UTC (permalink / raw)
To: Lars Erik Thorsplass <thorsplass@gmail.com>; +Cc: pgsql-sql
On 2004-07-20 15:34, Użytkownik Lars Erik Thorsplass napisał:
> My stored procedure "acl_check()" takes two integers as parameters.
> Param1 is the object id to check acl on, Param 2 is the object id of
> the user currently using the system. The procedure returns a positive
> number (1 or 3 ) if you have some kind of access to the object. As one
> might understand I want the returned value from the acl_check()
> procedure to be a part of the result set.
>
> Kinda like this:
>
> SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
> WHERE mode > 0;
>
> This gives me a: ERROR: column "mode" does not exist
You can't access column output alias in where clause. Instead you have
to use your function twice:
SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
WHERE acl_check( objects.obid, <user_id> ) > 0;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedures and "pseudo" fields..
@ 2004-07-20 14:11 Markus Bertheau <twanger@bluetwanger.de>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 7+ messages in thread
From: Markus Bertheau @ 2004-07-20 14:11 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Lars Erik Thorsplass <thorsplass@gmail.com>; pgsql-sql
В Втр, 20.07.2004, в 15:57, Tomasz Myrta пишет:
> On 2004-07-20 15:34, Użytkownik Lars Erik Thorsplass napisał:
> > My stored procedure "acl_check()" takes two integers as parameters.
> > Param1 is the object id to check acl on, Param 2 is the object id of
> > the user currently using the system. The procedure returns a positive
> > number (1 or 3 ) if you have some kind of access to the object. As one
> > might understand I want the returned value from the acl_check()
> > procedure to be a part of the result set.
> >
> > Kinda like this:
> >
> > SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
> > WHERE mode > 0;
> >
> > This gives me a: ERROR: column "mode" does not exist
>
> You can't access column output alias in where clause. Instead you have
> to use your function twice:
>
> SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
> WHERE acl_check( objects.obid, <user_id> ) > 0;
and if you properly marked the function STABLE and I am not mistaken,
then PostgreSQL is smart enough to execute the function only once per
row.
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedures and "pseudo" fields..
@ 2004-07-20 15:45 Scott Marlowe <smarlowe@qwest.net>
parent: Lars Erik Thorsplass <thorsplass@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2004-07-20 15:45 UTC (permalink / raw)
To: Lars Erik Thorsplass <thorsplass@gmail.com>; +Cc: pgsql-general@postgresql.org
On Tue, 2004-07-20 at 04:43, Lars Erik Thorsplass wrote:
> I have recently ventured into the exciting world of stored procedures,
> but I have now become lost.
>
> Background:
>
> Am currently working on access control in a web application. My goal
> is to process access control on the SQL level. This way if a row is in
> the result set, you have access to it, if not, you dont.
>
>
> Problem:
>
> My stored procedure "acl_check()" takes two integers as parameters.
> Param1 is the object id to check acl on, Param 2 is the object id of
> the user currently using the system. The procedure returns a positive
> numer (1 or 3 ) if you have some kind of access to the object. As one
> might understand I want the returned value from the acl_check()
> procedure to be a part of the result set.
>
> Kinda like this:
>
> SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
> WHERE mode > 0;
Here's the problem. In order to do the select, the query first needs to
run the where clause. I.e.:
select a as test from table where a > 50;
works, but
select a as test from table where test > 50;
fails. The reason is that when the where clause fires first, there IS
no test yet, as it hasn't been materialized. what you need to do is:
select custom_function(a,b) from table where custom_function(a,b) > 0;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] Stored procedures and "pseudo" fields..
@ 2004-07-20 22:03 Lars Erik Thorsplass <thorsplass@gmail.com>
parent: Scott Marlowe <smarlowe@qwest.net>
0 siblings, 1 reply; 7+ messages in thread
From: Lars Erik Thorsplass @ 2004-07-20 22:03 UTC (permalink / raw)
To: pgsql-sql
On Tue, 20 Jul 2004 09:45:06 -0600, Scott Marlowe <smarlowe@qwest.net> wrote:
> > Kinda like this:
> >
> > SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
> > WHERE mode > 0;
>
> Here's the problem. In order to do the select, the query first needs to
> run the where clause. I.e.:
>
> select a as test from table where a > 50;
>
> works, but
>
> select a as test from table where test > 50;
>
> fails. The reason is that when the where clause fires first, there IS
> no test yet, as it hasn't been materialized. what you need to do is:
>
> select custom_function(a,b) from table where custom_function(a,b) > 0;
>
>
Thanks for clearing that up. I just hoped there was some magic I could
sprinkle on my query to get away from the extra overhead of running
the procedure twice :)
Best regards..
L.E.Thorsplass
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedures and "pseudo" fields
@ 2004-08-10 13:44 Christoph Haller <ch@rodos.fzk.de>
parent: Lars Erik Thorsplass <thorsplass@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Christoph Haller @ 2004-08-10 13:44 UTC (permalink / raw)
To: Lars Erik Thorsplass <thorsplass@gmail.com>; pgsql-sql
Lars Erik Thorsplass wrote:
> On Tue, 20 Jul 2004 09:45:06 -0600, Scott Marlowe <smarlowe@qwest.net> wrote:
> > > Kinda like this:
> > >
> > > SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
> > > WHERE mode > 0;
> >
> > Here's the problem. In order to do the select, the query first needs to
> > run the where clause. I.e.:
> >
> > select a as test from table where a > 50;
> >
> > works, but
> >
> > select a as test from table where test > 50;
> >
> > fails. The reason is that when the where clause fires first, there IS
> > no test yet, as it hasn't been materialized. what you need to do is:
> >
> > select custom_function(a,b) from table where custom_function(a,b) > 0;
> >
> >
>
> Thanks for clearing that up. I just hoped there was some magic I could
> sprinkle on my query to get away from the extra overhead of running
> the procedure twice :)
>
> Best regards..
>
> L.E.Thorsplass
>
Pretty late, I know, but just for the record, AFAICS there is this magic as
SELECT * FROM (
SELECT *, acl_check( objects.obid, <user_id> ) AS mode FROM objects
) AS foo WHERE mode > 0;
Regards, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-08-10 13:44 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-20 10:43 Stored procedures and "pseudo" fields.. Lars Erik Thorsplass <thorsplass@gmail.com>
2004-07-20 13:34 ` Lars Erik Thorsplass <thorsplass@gmail.com>
2004-07-20 13:57 ` Tomasz Myrta <jasiek@klaster.net>
2004-07-20 14:11 ` Markus Bertheau <twanger@bluetwanger.de>
2004-07-20 15:45 ` Scott Marlowe <smarlowe@qwest.net>
2004-07-20 22:03 ` Lars Erik Thorsplass <thorsplass@gmail.com>
2004-08-10 13:44 ` Re: Stored procedures and "pseudo" fields Christoph Haller <ch@rodos.fzk.de>
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