niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » PostgreSQL
pg/plsql question
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
Author Message
Fred Blaise
*nix forums beginner


Joined: 15 Mar 2005
Posts: 12

PostPosted: Tue Mar 15, 2005 1:35 pm    Post subject: pg/plsql question Reply with quote
Hello all

I am trying to grant privs to a user on all tables. I think I understood
there was no command to do that.... :// so I wrote the following:

create or replace function granting() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
begin
v_user := "user"
v_schema := "public"
FOR t in select tablename from pg_tables where schemaname v_schema
LOOP
grant select on t to v_user;
END LOOP;
return 1;
end;
' LANGUAGE plpgsql;


I then login to psql, and do a \i myscript.sql. It returns CREATE
FUNCTION, but I cannot see anything. The tables are not granted, etc...
Also I am trying to find out how to debug this. How can I print out to
STDOUT the value of t for example?

Thanks for any help

Best,

fred
Back to top
John DeSoi
*nix forums Guru Wannabe


Joined: 14 Mar 2005
Posts: 116

PostPosted: Tue Mar 15, 2005 1:58 pm    Post subject: Re: pg/plsql question Reply with quote
Hi Fred,

On Mar 15, 2005, at 9:35 AM, Fred Blaise wrote:

Quote:
I am trying to grant privs to a user on all tables. I think I
understood
there was no command to do that.... :// so I wrote the following:

You can find some code to do this here:

http://pgedit.com/node/view/20

Quote:

I then login to psql, and do a \i myscript.sql. It returns CREATE
FUNCTION, but I cannot see anything. The tables are not granted, etc...
Also I am trying to find out how to debug this. How can I print out to
STDOUT the value of t for example?



Unfortunately, you can't print to stdout because the procedure is
executed on the database server. About the best you can do is to is to
use a raise log statement:

raise log ''t is %'', t;

This will write to the PostgreSQL log. Be sure to declare t -- this may
be one of your problems.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

http://www.postgresql.org/docs/faq
Back to top
Fred Blaise
*nix forums beginner


Joined: 15 Mar 2005
Posts: 12

PostPosted: Tue Mar 15, 2005 2:19 pm    Post subject: Re: pg/plsql question Reply with quote
On Tue, 2005-03-15 at 09:58 -0500, John DeSoi wrote:
Quote:
Hi Fred,

On Mar 15, 2005, at 9:35 AM, Fred Blaise wrote:

I am trying to grant privs to a user on all tables. I think I
understood
there was no command to do that.... :// so I wrote the following:

You can find some code to do this here:

http://pgedit.com/node/view/20
nice :)


I then login to psql, and do a \i myscript.sql. It returns CREATE
FUNCTION, but I cannot see anything. The tables are not granted, etc...
Also I am trying to find out how to debug this. How can I print out to
STDOUT the value of t for example?



Unfortunately, you can't print to stdout because the procedure is
executed on the database server. About the best you can do is to is to
use a raise log statement:

raise log ''t is %'', t;
Yes, that's what I thought... but oddly nothing gets written. I see

other things get written to the postgres log, but not those. I have
tried raise log and raise notice.
Quote:

This will write to the PostgreSQL log. Be sure to declare t -- this may
be one of your problems.
Declared now as varchar.


Just to make sure... Once the function is created, you would call it as
'execute function()' from psql, correct?
Quote:


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Thanks a lot


fred
Back to top
John DeSoi
*nix forums Guru Wannabe


Joined: 14 Mar 2005
Posts: 116

PostPosted: Tue Mar 15, 2005 2:24 pm    Post subject: Re: pg/plsql question Reply with quote
On Mar 15, 2005, at 10:19 AM, Fred Blaise wrote:

Quote:
Just to make sure... Once the function is created, you would call it as
'execute function()' from psql, correct?



Try: select function();

As a top level SQL command, EXECUTE is for executing prepared
statements:

http://www.postgresql.org/docs/8.0/interactive/sql-execute.html

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
Back to top
Tom Lane
*nix forums Guru


Joined: 24 Mar 2005
Posts: 2070

PostPosted: Tue Mar 15, 2005 2:34 pm    Post subject: Re: pg/plsql question Reply with quote
Fred Blaise <fred.blaise@excilan.com> writes:
Quote:
On Tue, 2005-03-15 at 09:58 -0500, John DeSoi wrote:
raise log ''t is %'', t;

Yes, that's what I thought... but oddly nothing gets written.

Fred, your original example made it look like you were writing "
(one double quote mark) where what you need to write is ''
(two single quote marks). The reason is that you are trying to
embed a single quote mark in the value of a string literal.
(If you are using PG 8.0 I'd suggest adopting the dollar-quoting
style for entering the function body, instead.)

Another problem I noticed is you were leaving off required
statement-ending semicolons, which could also prevent the plpgsql
parser from recognizing the RAISE command properly.

You might try something simpler just to get your feet wet:

create function hello_world(text) returns text as '
begin
raise notice ''I got %'', $1;
return $1;
end' language plpgsql;

select hello_world('Hi there!');

Once you get past that you'll have some idea about the quote marks
anyway ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Back to top
Fred Blaise
*nix forums beginner


Joined: 15 Mar 2005
Posts: 12

PostPosted: Tue Mar 15, 2005 4:18 pm    Post subject: Re: pg/plsql question Reply with quote
While I have accomplished what I needed with the pgedit script given by
John, I am still curious as to why mine is not working...
Here is the latest version:

/* */
create or replace function fred_on_all() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
v_t varchar;
begin
v_user := ''user'';
v_schema := ''public'';
FOR v_t in select tablename from pg_catalog.pg_tables where
schemaname = v_schema
LOOP
raise notice ''v_t is %'', t;
END LOOP;
return 1;
end;
' LANGUAGE 'plpgsql';

Please note that all ticks above are single ticks.

Here is what I do to execute it:
excilan=# \i grant.sql
CREATE FUNCTION
excilan=# select fred_on_all();
ERROR: missing ".." at end of SQL expression
CONTEXT: compile of PL/pgSQL function "fred_on_all" near line 8

If anyone could shade some lights...

Much appreciated.

fred

On Tue, 2005-03-15 at 15:35 +0100, Fred Blaise wrote:
Quote:
Hello all

I am trying to grant privs to a user on all tables. I think I understood
there was no command to do that.... :// so I wrote the following:

create or replace function granting() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
begin
v_user := "user"
v_schema := "public"
FOR t in select tablename from pg_tables where schemaname > v_schema
LOOP
grant select on t to v_user;
END LOOP;
return 1;
end;
' LANGUAGE plpgsql;


I then login to psql, and do a \i myscript.sql. It returns CREATE
FUNCTION, but I cannot see anything. The tables are not granted, etc...
Also I am trying to find out how to debug this. How can I print out to
STDOUT the value of t for example?

Thanks for any help

Best,

fred
Back to top
Ragnar Hafstaš
*nix forums beginner


Joined: 01 Mar 2005
Posts: 37

PostPosted: Tue Mar 15, 2005 5:00 pm    Post subject: Re: pg/plsql question Reply with quote
On Tue, 2005-03-15 at 18:18 +0100, Fred Blaise wrote:
Quote:
While I have accomplished what I needed with the pgedit script given by
John, I am still curious as to why mine is not working...
Here is the latest version:

/* */
create or replace function fred_on_all() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
v_t varchar;
begin
v_user := ''user'';
v_schema := ''public'';
FOR v_t in select tablename from pg_catalog.pg_tables where
schemaname = v_schema
LOOP
raise notice ''v_t is %'', t;
END LOOP;
return 1;
end;
' LANGUAGE 'plpgsql';

Please note that all ticks above are single ticks.

Here is what I do to execute it:
excilan=# \i grant.sql
CREATE FUNCTION
excilan=# select fred_on_all();
ERROR: missing ".." at end of SQL expression
CONTEXT: compile of PL/pgSQL function "fred_on_all" near line 8

taken from
http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

<quote>
Note: The PL/pgSQL parser presently distinguishes the two kinds of FOR
loops (integer or query result) by checking whether the target variable
mentioned just after FOR has been declared as a record or row variable.
If not, it's presumed to be an integer FOR loop. This can cause rather
nonintuitive error messages when the true problem is, say, that one has
misspelled the variable name after the FOR. Typically the complaint will
be something like missing ".." at end of SQL expression.
</quote>

try (untested):

create or replace function fred_on_all() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
v_rec RECORD;
begin
v_user := ''user'';
v_schema := ''public'';
FOR v_rec in select tablename from pg_catalog.pg_tables where
schemaname = v_schema
LOOP
raise notice ''v_t is %'', v_REC.tablename;
END LOOP;
return 1;
end;
' LANGUAGE 'plpgsql';

gnari



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Back to top
Fred Blaise
*nix forums beginner


Joined: 15 Mar 2005
Posts: 12

PostPosted: Wed Mar 16, 2005 7:19 am    Post subject: Re: pg/plsql question Reply with quote
that worked Smile thanks for your input

fred

On Tue, 2005-03-15 at 18:00 +0000, Ragnar Hafstaš wrote:
Quote:
On Tue, 2005-03-15 at 18:18 +0100, Fred Blaise wrote:
While I have accomplished what I needed with the pgedit script given by
John, I am still curious as to why mine is not working...
Here is the latest version:

/* */
create or replace function fred_on_all() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
v_t varchar;
begin
v_user := ''user'';
v_schema := ''public'';
FOR v_t in select tablename from pg_catalog.pg_tables where
schemaname = v_schema
LOOP
raise notice ''v_t is %'', t;
END LOOP;
return 1;
end;
' LANGUAGE 'plpgsql';

Please note that all ticks above are single ticks.

Here is what I do to execute it:
excilan=# \i grant.sql
CREATE FUNCTION
excilan=# select fred_on_all();
ERROR: missing ".." at end of SQL expression
CONTEXT: compile of PL/pgSQL function "fred_on_all" near line 8

taken from
http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures..html#PLPGSQL-RECORDS-ITERATING

quote
Note: The PL/pgSQL parser presently distinguishes the two kinds of FOR
loops (integer or query result) by checking whether the target variable
mentioned just after FOR has been declared as a record or row variable.
If not, it's presumed to be an integer FOR loop. This can cause rather
nonintuitive error messages when the true problem is, say, that one has
misspelled the variable name after the FOR. Typically the complaint will
be something like missing ".." at end of SQL expression.
/quote

try (untested):

create or replace function fred_on_all() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
v_rec RECORD;
begin
v_user := ''user'';
v_schema := ''public'';
FOR v_rec in select tablename from pg_catalog.pg_tables where
schemaname = v_schema
LOOP
raise notice ''v_t is %'', v_REC.tablename;
END LOOP;
return 1;
end;
' LANGUAGE 'plpgsql';

gnari

Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
The time now is Fri Jan 09, 2009 6:36 am | All times are GMT
navigation Forum index » Databases » PostgreSQL
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Newbie question: How to forward a domain to a mailbox? leei Postfix 0 Fri Aug 24, 2007 4:55 pm
No new posts configuration question for httpd Karl Wang Apache 1 Fri Jul 21, 2006 2:10 pm
No new posts nim problem/question Ron AIX 0 Fri Jul 21, 2006 1:57 pm
No new posts question for JAVA developer who r using postgres sql as b... deepak pal PostgreSQL 1 Fri Jul 21, 2006 9:00 am
No new posts Encryption Question dtuttle1@gmail.com Berkeley DB 2 Thu Jul 20, 2006 10:09 pm

Dominios | Money News | Credit Cards UK | Car Credit | Best Credit Cards
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.3169s ][ Queries: 16 (0.1709s) ][ GZIP on - Debug on ]