[SQL] information_schema for all users

[Luca Giandoso]

I wold like to make a plpgsql function that return column names and
their data types of a specific table.
I have done this using:

CREATE OR REPLACE FUNCTION _get_table_definition(refcursor) RETURNS
refcursor SECURITY DEFINER AS
$$
DECLARE
BEGIN
   OPEN $1 FOR SELECT column_name as field_name, data_type as
field_type FROM information_schema.columns WHERE
table_name='_table_name';
   RETURN $1;
END;
$$ LANGUAGE plpgsql;

but it works only with the database owner although i have used
"SECURITY DEFINER".
How can I make it works for everyone that calls the function?

I use PostgreSQL 8.1.4

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] distinct with sum

[mbobi]

I need to list products from the table of ordered products with ordered 
quantities. Each product should be there only once but quantity should be a sum 
from all the same products from the table.
I prepared some query but in quantity column I can see only amount from one 
record. I need to have sum there.

The query:
select distinct on (id_prod) id_prod, id_unit, sum(quantity) as result from 
products where determinant = false group by id_prod, id_unit

Thanks in advance for your help.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] distinct with sum

[Hélder M . Vieira]

I need to list products from the table of ordered products with ordered 
quantities. Each product should be there only once but quantity should be a 
sum from all the same products from the table.
I prepared some query but in quantity column I can see only amount from 
one record. I need to have sum there.


The query:
select distinct on (id_prod) id_prod, id_unit, sum(quantity) as result 
from products where determinant = false group by id_prod, id_unit





select id_prod, id_unit, sum(quantity) as result
from products
where determinant = false
group by id_prod, id_unit



Hélder M. Vieira



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] information_schema for all users

[Michael Fuhr]

On Sun, Jul 09, 2006 at 09:52:35AM +0200, Luca Giandoso wrote:
> I wold like to make a plpgsql function that return column names and
> their data types of a specific table.

Do you have a reason for returning a cursor instead of SETOF some
type?  In versions prior to 8.1 you could create a custom type for
the return columns; in 8.1 you could use OUT parameters and return
SETOF record.

[snip function that returns a cursor over information_schema.columns]

> but it works only with the database owner although i have used
> "SECURITY DEFINER".

The information_schema privilege checks are based on current_user,
which is apparently evaluated when you fetch rows from the cursor,
not when you open the cursor.  Here's a simple example; we'll create
the following function as user alice:

  CREATE FUNCTION testfunc(refcursor) RETURNS refcursor AS $$
  BEGIN
  RAISE INFO 'current_user = %', current_user;
  OPEN $1 FOR SELECT current_user;
  RETURN $1;
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;

First we'll call the function as alice; notice that the current_user
displayed by the RAISE statement is the same as the current_user
fetched by the cursor:

  test=> BEGIN; SELECT testfunc('curs'); FETCH curs; COMMIT;
  BEGIN
  INFO:  current_user = alice
   testfunc 
  --
   curs
  (1 row)
  
   current_user 
  --
   alice
  (1 row)
  
  COMMIT

Now we'll call the same function as user bob; notice that the
current_user displayed by the RAISE statement is "alice" because
of SECURITY DEFINER but that the current_user displayed when fetching
from the cursor is "bob":

  test=> \c - bob
  Password for user bob: 
  You are now connected as new user "bob".
  test=> BEGIN; SELECT testfunc('curs'); FETCH curs; COMMIT;
  BEGIN
  INFO:  current_user = alice
   testfunc 
  --
   curs
  (1 row)
  
   current_user 
  --
   bob
  (1 row)
  
  COMMIT

I don't know if this behavior is intentional but that's how it
currently works.  You could avoid it by returning SETOF some type
rather than a cursor, or you could query the PostgreSQL system
catalogs directly instead of using information_schema.  If you're
returning the results of a simple query, and if you can make that
query work without SECURITY DEFINER, then you could use a view
instead of a function.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] SELECT substring with regex

[Emils]

2006/7/7, T E Schmitz <[EMAIL PROTECTED]>:


valid entries would be:
"28mm F2.8" (prime lens)
"30-70mm F4" (zoom lens)
"30-70mm F2.8" (zoom lens)
"30-100mm F4.5-5.6" (zoom lens with variable speed)


If these are the cases, wouldn't the regex be simply:

"^[\d\-]+mm" for BASE

"^[\d\-]+mm (.+)$" for SUFFIX

Or are you having to deal with malformatted data too (like "30 -70mm"
or "28 mm ")?

Emils

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match