pg/plsql question

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

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

On Tue, 2005-03-15 at 09:58 -0500, John DeSoi wrote:

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.

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?

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

Thanks a lot

fred

On Mar 15, 2005, at 10:19 AM, Fred Blaise wrote:

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

Fred Blaise <fred.blaise@excilan.com> writes:

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

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 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

that worked :) thanks for your input

fred