Thread: Re: Wanted: Want to hide data by using PL/PGSQL functio

This sounds like a perfect situation to use a view.  With the view you can limit the data that can be seen by the user.

Duane

-----Original Message-----
From: Gellert, Andre [mailto:AGellert@ElectronicPartner.de]
Sent: Wednesday, July 21, 2004 10:32 AM
To: 'pgsql-general@postgresql.org'
Subject: [GENERAL] Wanted: Want to hide data by using PL/PGSQL functions

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