From: | Adam Witney <awitney(at)sghms(dot)ac(dot)uk> |
---|---|
To: | "Gellert, Andre" <AGellert(at)ElectronicPartner(dot)de>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Wanted: Want to hide data by using PL/PGSQL functions |
Date: | 2004-07-21 17:57:35 |
Message-ID: | BD246D1F.362F9%awitney@sghms.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
To return a result set use SETOF, like so
CREATE FUNCTION test() RETURNS SETOF text AS '
To allow access to the tables only through a function, take a look at
declaring your functions with SECURITY DEFINER
CREATE FUNCTION test() RETURNS SETOF text SECURITY DEFINER AS '
.. Inside the function you will now have permissions of the user that
created the function. See here for details
http://www.postgresql.org/docs/7.4/interactive/sql-createfunction.html
Also this may be useful
http://techdocs.postgresql.org/guides/SetReturningFunctions
HTH
adam
> Hello,
> I have following problem:
>
> A user "xy" shouldn't have any rights to a table,
> but needs data from the content of the table.
> My idea was to setup a PL/PGSQL procedure to fetch the
> data from the table, so that the user only is allowed to
> access the procedure. I also tried using a SQL function,
> but this doesn't work, too.
> Working with views may be a solution - or are e.g. cursors
> in pl/pgsql the solution ??
> The problem i run into is, that although i can read the data
> and return it, I can not return more than one row each
> function call. Is it possible to return a whole resultset ?
>
> My last try was:
>
> drop function test(int);
> create function test(int) returns table_name as '
> select * from table_name where column_name1>= $1
> order by column_name1;
> ' language sql;
> select * from test(1) ;
>
> The pl/pgsql variant:
>
>
> drop function test();
> CREATE FUNCTION test() RETURNS text AS '
> declare
> target table_name%ROWTYPE;
> begin
> select * into target from table_name ;
> return target.column_name1 || target.column_name2;
> end;
> ' LANGUAGE plpgsql;
> select test();
> But in PL/pgsql i am not able to return a cursor or something like this
> and I am not able to return more than one row.
>
> So i have got 2 maybe solutions, but none of them works.
>
> Has anyone a hint, how to "hide" original tables and making their data
> selectable to some users ? The result really should be a
> select a.* , b.* from a,b where a.state!="imgonewild" ....
>
> Thanks in advance,
> Andre
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
From | Date | Subject | |
---|---|---|---|
Next Message | Duane Lee - EGOVX | 2004-07-21 18:04:17 | Re: Wanted: Want to hide data by using PL/PGSQL functio |
Previous Message | Gellert, Andre | 2004-07-21 17:32:15 | Wanted: Want to hide data by using PL/PGSQL functions |