[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

Re: [SQL] retrieving a serial number

[Joachim Trinkwitz]

"Robert B. Easter" <[EMAIL PROTECTED]> writes:

> You can do something like this:
> $var = SELECT nextval('seq_name');
> INSERT INTO master (id, ...) VALUES ($var, ...);
> INSERT INTO slave1 (id, ...) VALUES ($var, ...);

Great feaure. What to do when importing some previous data from ascii
files -- can you automatically give a serial number in this way to?

Thanks in advance,
joachim

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

[Tom Lane]

Greg Wickham <[EMAIL PROTECTED]> writes:
> 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.

You need a function that will return true if any of the elements of
an int4 array is equal to a particular int4 value.  See contrib/array/
for one way to do it.

regards, tom lane

[SQL] case insensitive search

[Joern Muehlencord]

Hello together,

how can I handle case insensitive search in a table?

 

-- 
Linux is like wigwam - no windows, no gates, apache inside.
In diesem Sinne
  Joern

[SQL] error 3122 in ACCESS -

[Schlobohm, Jack]

I have a query which uses an expression... dividing "1" by a number field in
a static table.

I changed the expression to divide by a different number field from the same
table. This caused an error. 

Error 3122: I tried to execute a query that doesn't include the specific
expression < * > as part of an aggregate function. 

The possible cause is listed as: I didn't enter an aggregate function in the
TRANSFORM statement.

OK - The only thing different from this query and the one that worked is
which field is included in the expression. The expression is { NewFieldName:
(1/[FieldName])*([FieldCreatedbyanEarlierExpressioninQuery]) }.

1st - What is a transform statement?
2nd- What is an aggregate function?
3rd - Would I need to change anything is SQL... and what?
4th- Why wouldn't the same expression work with a different field?



Jack Schlobohm

[SQL] isnull or coalesce isn't working

[Jochen Schmidt]

HI
just started using postgres I want to do a select like the following.
I have a table with two columns a and b, in b sometimes data are missing. 
I want to select column b, and if data are missing instead column a
as one column in my select.
I did:
SELECT ifnull(a,b) FROM table;

==>ERROR:  No such function 'ifnull' with the specified attributes

tried the same thing with isnull and coalesce, same error.
(on my machine: postgresql version 6.4.2)

help?! thanks, jochen

-- 

.* .
. Dipl. Geogr. Jochen Schmidt*  | |.
.   | | | |.
.   Department of Geography  phone +49/0228/739646  | | | |  * .
. University of Bonn fax   +49/0228/739099  | |_| | | |. 
.   Meckenheimer Allee 166 [EMAIL PROTECTED]   \___  |_| |.
.  D - 53115 Bonn http://slide.giub.uni-bonn.de/~jochen |  ___/.
.   | |.  

Re: [SQL] case insensitive search

[[EMAIL PROTECTED]]

Joern,
select myfield from tablea where lower(myfield) = 'mysearch';
or 
select myfield from tablea where myfield ~* 'mysearch';


Troy

> 
> Hello together,
> 
> how can I handle case insensitive search in a table?
> 
>  
> 
> -- 
> Linux is like wigwam - no windows, no gates, apache inside.
> In diesem Sinne
>   Joern
> 
> 
>