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 2 1598
It sounds like you want a table function: http://techdocs.postgresql.org/guide...rningFunctions
On Wed, 21 Jul 2004, Gellert, Andre wrote: 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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
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/i...efunction.html
Also this may be useful http://techdocs.postgresql.org/guide...rningFunctions
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.
---------------------------(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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dorthe Luebbert |
last post by:
Hi,
we have to convert a quite large ISO-application (Mysql4, PHP5) to
UTF8. We found out so far that there is no collation for MySQL which
is able to sort all character sets correctly. So this...
|
by: Clay Luther |
last post by:
I had been having good luck writing stored procs in C++ with Postgres.
But, today, I needed to include the SPI interface to help a trigger
along, aka
#include "executor/spi.h"
and this is...
|
by: Thomas LeBlanc |
last post by:
Is there a tool for designing databases(ERD to data Design) and being able
to generate scripts or databases from the tool.
Visio does a good job, but does not have a driver for PostgreSQL. The...
|
by: Jenny Zhang |
last post by:
I am running OSDL-dbt1 - an e-commerce workload
(http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/)
against PostgreSQL: 7.3.3. During the test, I saw a lot of...
|
by: Capstar |
last post by:
Hi,
I am working on an application, which will run embedded without an OS.
The app is build up out of a couple of well defined parts. At first I
wanted to keep those parts seperated and use...
|
by: Együd Csaba |
last post by:
Hi All,
I'd like to "compress" the following two filter expressions into one -
assuming that it makes sense regarding query execution performance.
.... where (adate LIKE "2004.01.10 __:30" or...
|
by: Steve Atkins |
last post by:
I have an application where each user session opens and maintains a
long-lived connection to the postgresql backend database.
I need to keep a small amount of information (things like a unique...
|
by: Google Mike |
last post by:
I had RH9 Linux. It came with pgSQL, but I couldn't seem to figure out
how to get PL/pgSQL going. I read the HTML documentation that came
with it and was confused until I tried a few different...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |