Skip site navigation (1) Skip section navigation (2)

Wanted: Want to hide data by using PL/PGSQL functions

From: "Gellert, Andre" <AGellert(at)ElectronicPartner(dot)de>
To: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Wanted: Want to hide data by using PL/PGSQL functions
Date: 2004-07-21 17:32:15
Message-ID: 5CE421247C32BA4EAB74AD3D5E020E28038426EC@muenchen.ep.de (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Adam WitneyDate: 2004-07-21 17:57:35
Subject: Re: Wanted: Want to hide data by using PL/PGSQL functions
Previous:From: Markus WollnyDate: 2004-07-21 16:30:28
Subject: Re: [OpenFTS-general] AW: tsearch2, ispell, utf-8 and german special characters

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group