[SQL] plpgsql variable substitution problem ...

[Greg Wickham]

[Hopefully the correct list this time :)]

Howdy.  Creating a function to access different tables depending
on the arguments. However, am having excessive problems.

Current code is as follows:

CREATE FUNCTION access(varchar,varchar) RETURNS INT4 AS '
DECLARE
lcl_field ALIAS FOR $1;
lcl_table ALIAS FOR $2;
lcl_max INT;
BEGIN

SELECT max(lcl_field) INTO lcl_max FROM lcl_table;

RETURN lcl_max;

END;

' LANGUAGE 'plpgsql';

However, if I execute the following code

SELECT getsequence('f1','t1');

I get an error:

  psql:sequence.db:35: ERROR:  parser: parse error at or near "$2"

(What I really want is to execute a "SELECT max(f1) FROM f2")

The output from the postmaster is:

000528.19:45:45.682  [9553] CommitTransactionCommand
000528.19:45:45.713  [9553] StartTransactionCommand
000528.19:45:45.713  [9553] query: SELECT access('f1','t1'));
000528.19:45:45.717  [9553] ProcessQuery
000528.19:45:45.725  [9553] query: SELECT  max( $1 ) FROM  $2
000528.19:45:45.726  [9553] ERROR:  parser: parse error at or near "$2"
000528.19:45:45.726  [9553] DEBUG:  Last error occured while executing PL/pgSQL 
function getsequence
000528.19:45:45.726  [9553] DEBUG:  line 6 at select into variables
000528.19:45:45.726  [9553] AbortCurrentTransaction

Any ideas? I've fiddled with the variables and procedures a few
times but I can't quite get a breakthrough.

tia,

   -Greg

------
www.geelong.com   Greg Wickham  
 P.O. Box 1426[EMAIL PROTECTED]
Geelong VIC 3220 Ph: (+61 407) 854 566
--
thEsepRetzelsareMakingmetHirstythEsepRetzelsareMakingmetHirstythEs

[SQL] Query about using arrays (for accessing pg_group)

[Greg Wickham]

Howdy,

Am trying to implement a user/group based security model for
accessing a database which is tightly integrated with the postgresql
security model.

The problem I am having is that I am not sure how to check which
groups a user is in.

For example, if I have a user (with id of 21) in a group, I'm not
sure how to start off with the user id and pull out the group name
using normal sql.

If I try the half intuitive approach

z=> select groname from pg_group where grolist = 21;
ERROR:  Unable to identify an operator '=' for types '_int4' and 'int4'
You will have to retype this query using an explicit cast

The cause of my problem is the use of an array to store the user id's
associated with a group. Not sure how to get around this.

Any ideas?

Please cc replies to me as I think I've fallen off this list.

tia,

   -Greg

--
www.geelong.com   Greg Wickham  
 P.O. Box 1426[EMAIL PROTECTED]
Geelong VIC 3220 Ph: (+61 407) 854 566
--
wheNufiNdu'RselFindAngeRwheNu'rThreAtenEdbyAstrAngeRwheNitlOoksLik