Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg/plsql question

pg/plsql question

From
Fred Blaise
Date:
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
Attachment

Re: pg/plsql question

From
John DeSoi
Date:
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

Re: pg/plsql question

From
Fred Blaise
Date:
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
Attachment

Re: pg/plsql question

From
John DeSoi
Date:
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

Re: pg/plsql question

From
Tom Lane
Date:
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

Re: pg/plsql question

From
Fred Blaise
Date:
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:
> 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
Attachment

Re: pg/plsql question

From
Ragnar Hafstað
Date:
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

Re: pg/plsql question

From
Fred Blaise
Date:
that worked :) thanks for your input

fred

On Tue, 2005-03-15 at 18:00 +0000, Ragnar Hafstað wrote:
> 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
>
>
Attachment