grant all privileges on database test to auser

As far as I can tell this does nothing. Intuitively this command suggests
that the auser would be able to access and modify the database test in
anyway. It would also suggest that as new tables for the database auser
would automatically have access to them.

Instead it appears that we have to still individually grant access to
tables on an individual basis. It seems to me that if it did pass these
blanket privileges on it would be very useful and make administration a
lot easier. While it is not hard to initially grant the individual access
(i am looking for a script) it is a pain in the butt to maintain. Is this
in fact how it works?

While I am sure someone will reply that by forcing us to individually
grant access on a table by table basis you are inherently forcing the
administrators to maintain high security - which is a good thing. However
i would then reply that in fact because most db admins are lazy this means
that they won't even bother and just use the super user privilege instead.




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

developer@wexwarez.com wrote:
> grant all privileges on database test to auser
>
> As far as I can tell this does nothing. Intuitively this command suggests
> that the auser would be able to access and modify the database test in
> anyway. It would also suggest that as new tables for the database auser
> would automatically have access to them.
>
>
It just grants all privileges for a database object to auser.

You should check out the grant manager in Lightning Admin for
Postgresql, it visually shows everything and makes
it a bitter easier to see how it works.

http://www.amsoftwaredesign.com/onli...nt_manager.htm

In the screenshot the cells that are green are the ones that can be set
for a particular object, and for a database it's just
two. So the grant statement you used above did indeed do something, but
only for a database object.

Hope this helps.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration
Only $9.99 through January 1st 2007


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

I am trying to loop through some data and then run insert some of the
resulting data into a new table. I can create the function but when I run
it i get the error:

ERROR: query "SELECT 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id , patient_contact_responsible_party_id ,
insurer_network_responsible_party_id, type, status)
values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
, $7 , 'Other', 'ACCEPTED'" returned 11 columns
SQL state: 42601
Context: PL/pgSQL function "add_missing_slrps" line 20 at execute statement

I don't understand what the "returned 11 columns" means. I am inserting
10 and i counted and it all matches.


Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
is where the errors starts

CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
DECLARE
data RECORD;
paymentId int;
BEGIN
RAISE NOTICE 'Start loop...';

FOR data IN select slra.company_id, slra.create_date,
slra.service_line_responsibility_id,
slr.insurance_policy_responsible_party_id,
slr.patient_responsible_party_id,
slr.patient_contact_responsible_party_id,
insurer_service_center.insurer_network_id
from
..
..
..
..
..
..
..
..
..
LOOP
-- Now "data" has one record
EXECUTE 'select nextval(''seq_payment'') ' into paymentId;

EXECUTE 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id patient_contact_responsible_party_id ,
insurer_network_responsible_party_id, type, status)
values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
data.company_id, data.create_date , data.patient_responsible_party_id ,
data.patient_contact_responsible_party_id , data.insurer_network_id,
'Other', 'ACCEPTED';


END LOOP;

RAISE NOTICE 'Done loop .';
RETURN 1;
END;
$$ LANGUAGE plpgsql;
select add_missing_slrps() ;


I assumed using the '%' symbol will automatically use the real value.
Like if it is a date it will handle it like a java prepared statement. Am
I wrong?

I have tried all kinds of things but I truly have no idea what the problem
is.
thanks




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

http://archives.postgresql.org/

developer@wexwarez.com wrote:
> I am trying to loop through some data and then run insert some of the
> resulting data into a new table. I can create the function but when I run
> it i get the error:
>
> ERROR: query "SELECT 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id , patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
> SQL state: 42601
> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute statement
>
> I don't understand what the "returned 11 columns" means. I am inserting
> 10 and i counted and it all matches.
>
>
> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
> is where the errors starts
>
> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
> DECLARE
> data RECORD;
> paymentId int;
> BEGIN
> RAISE NOTICE 'Start loop...';
>
> FOR data IN select slra.company_id, slra.create_date,
> slra.service_line_responsibility_id,
> slr.insurance_policy_responsible_party_id,
> slr.patient_responsible_party_id,
> slr.patient_contact_responsible_party_id,
> insurer_service_center.insurer_network_id
> from
> .
> .
> .
> .
> .
> .
> .
> .
> .
> LOOP
> -- Now "data" has one record
> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>
> EXECUTE 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
> data.company_id, data.create_date , data.patient_responsible_party_id ,
> data.patient_contact_responsible_party_id , data.insurer_network_id,
> 'Other', 'ACCEPTED';
>
>
> END LOOP;
>
> RAISE NOTICE 'Done loop .';
> RETURN 1;
> END;
> $$ LANGUAGE plpgsql;
> select add_missing_slrps() ;
>
>
> I assumed using the '%' symbol will automatically use the real value.
> Like if it is a date it will handle it like a java prepared statement. Am
> I wrong?
>
I believe you are wrong. the EXECUTE is being given 11 columns, it
expects 1. I think you need to form your execute query like;

EXECUTE 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id patient_contact_responsible_party_id ,
insurer_network_responsible_party_id, type, status)
values (' || quote_ident(paymentId) || ',' || ...

Something of that fashion.


> I have tried all kinds of things but I truly have no idea what the problem
> is.
> thanks
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>
>


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

http://archives.postgresql.org/

On Wednesday 20 December 2006 7:36 pm, developer@wexwarez.com wrote:
> I am trying to loop through some data and then run insert some of the
> resulting data into a new table. I can create the function but when I run
> it i get the error:
>
> ERROR: query "SELECT 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id , patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
> SQL state: 42601
> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute statement
>
> I don't understand what the "returned 11 columns" means. I am inserting
> 10 and i counted and it all matches.
>
>
> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
> is where the errors starts
>
> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
> DECLARE
> data RECORD;
> paymentId int;
> BEGIN
> RAISE NOTICE 'Start loop...';
>
> FOR data IN select slra.company_id, slra.create_date,
> slra.service_line_responsibility_id,
> slr.insurance_policy_responsible_party_id,
> slr.patient_responsible_party_id,
> slr.patient_contact_responsible_party_id,
> insurer_service_center.insurer_network_id
> from
> .
> .
> .
> .
> .
> .
> .
> .
> .
> LOOP
> -- Now "data" has one record
> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>
> EXECUTE 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id patient_contact_responsible_party_id ,
^^^^ no comma

> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
> data.company_id, data.create_date , data.patient_responsible_party_id ,
> data.patient_contact_responsible_party_id , data.insurer_network_id,
> 'Other', 'ACCEPTED';
>
>
> END LOOP;
>
> RAISE NOTICE 'Done loop .';
> RETURN 1;
> END;
> $$ LANGUAGE plpgsql;
> select add_missing_slrps() ;
>
>
> I assumed using the '%' symbol will automatically use the real value.
> Like if it is a date it will handle it like a java prepared statement. Am
> I wrong?
>
> I have tried all kinds of things but I truly have no idea what the problem
> is.
> thanks
>

See inline comment, but I think you are missing a comma in your columns list.

--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> developer@wexwarez.com wrote:
>> I am trying to loop through some data and then run insert some of the
>> resulting data into a new table. I can create the function but when I
>> run
>> it i get the error:
>>
>> ERROR: query "SELECT 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id , patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
>> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
>> SQL state: 42601
>> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute
>> statement
>>
>> I don't understand what the "returned 11 columns" means. I am inserting
>> 10 and i counted and it all matches.
>>
>>
>> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
>> is where the errors starts
>>
>> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
>> DECLARE
>> data RECORD;
>> paymentId int;
>> BEGIN
>> RAISE NOTICE 'Start loop...';
>>
>> FOR data IN select slra.company_id, slra.create_date,
>> slra.service_line_responsibility_id,
>> slr.insurance_policy_responsible_party_id,
>> slr.patient_responsible_party_id,
>> slr.patient_contact_responsible_party_id,
>> insurer_service_center.insurer_network_id
>> from
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> LOOP
>> -- Now "data" has one record
>> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>>
>> EXECUTE 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
>> data.company_id, data.create_date , data.patient_responsible_party_id ,
>> data.patient_contact_responsible_party_id , data.insurer_network_id,
>> 'Other', 'ACCEPTED';
>>
>>
>> END LOOP;
>>
>> RAISE NOTICE 'Done loop .';
>> RETURN 1;
>> END;
>> $$ LANGUAGE plpgsql;
>> select add_missing_slrps() ;
>>
>>
>> I assumed using the '%' symbol will automatically use the real value.
>> Like if it is a date it will handle it like a java prepared statement.
>> Am
>> I wrong?
>>
> I believe you are wrong. the EXECUTE is being given 11 columns, it
> expects 1. I think you need to form your execute query like;
>
> EXECUTE 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (' || quote_ident(paymentId) || ',' || ...
>
> Something of that fashion.
>
>
>> I have tried all kinds of things but I truly have no idea what the
>> problem
>> is.
>> thanks
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org/
>>
>>
>>
>
>
Thanks for replying. So the '%' symbol can not be used with an EXECUTE
'INSERT statement. I thought this was exactly what it was for?


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

http://archives.postgresql.org/

> On Wednesday 20 December 2006 7:36 pm, developer@wexwarez.com wrote:
>> I am trying to loop through some data and then run insert some of the
>> resulting data into a new table. I can create the function but when I
>> run
>> it i get the error:
>>
>> ERROR: query "SELECT 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id , patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
>> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
>> SQL state: 42601
>> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute
>> statement
>>
>> I don't understand what the "returned 11 columns" means. I am inserting
>> 10 and i counted and it all matches.
>>
>>
>> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
>> is where the errors starts
>>
>> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
>> DECLARE
>> data RECORD;
>> paymentId int;
>> BEGIN
>> RAISE NOTICE 'Start loop...';
>>
>> FOR data IN select slra.company_id, slra.create_date,
>> slra.service_line_responsibility_id,
>> slr.insurance_policy_responsible_party_id,
>> slr.patient_responsible_party_id,
>> slr.patient_contact_responsible_party_id,
>> insurer_service_center.insurer_network_id
>> from
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> LOOP
>> -- Now "data" has one record
>> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>>
>> EXECUTE 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id patient_contact_responsible_party_id ,
> ^^^^ no comma
>
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
>> data.company_id, data.create_date , data.patient_responsible_party_id ,
>> data.patient_contact_responsible_party_id , data.insurer_network_id,
>> 'Other', 'ACCEPTED';
>>
>>
>> END LOOP;
>>
>> RAISE NOTICE 'Done loop .';
>> RETURN 1;
>> END;
>> $$ LANGUAGE plpgsql;
>> select add_missing_slrps() ;
>>
>>
>> I assumed using the '%' symbol will automatically use the real value.
>> Like if it is a date it will handle it like a java prepared statement.
>> Am
>> I wrong?
>>
>> I have tried all kinds of things but I truly have no idea what the
>> problem
>> is.
>> thanks
>>
>
> See inline comment, but I think you are missing a comma in your columns
> list.
>
> --
> Adrian Klaver
> aklaver@comcast.net
>

Adrian- It must have been copied over wrong or something because I just
checked it over and over and that comma is there and the result is the
same.


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

http://www.postgresql.org/docs/faq

developer@wexwarez.com wrote:
>> I believe you are wrong. the EXECUTE is being given 11 columns, it
>> expects 1. I think you need to form your execute query like;
>>
>> EXECUTE 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (' || quote_ident(paymentId) || ',' || ...
>>
>> Something of that fashion.

> Thanks for replying. So the '%' symbol can not be used with an EXECUTE
> 'INSERT statement. I thought this was exactly what it was for?

No - the RAISE statement takes that format, nothing else.

However, you only need the EXECUTE for statements that you need planned
every time they are called, e.g. can have their table-name changed. You
should just be able to write:
INSERT INTO ... VALUES (paymentId, 0.0, data.create_date ...)

P.S. - you can remove much of a message when you reply, that way people
can quickly follow the new parts of a message-thread.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> developer@wexwarez.com wrote:
>>> I believe you are wrong. the EXECUTE is being given 11 columns, it
>>> expects 1. I think you need to form your execute query like;
>>>
>>> EXECUTE 'INSERT INTO payment (
>>> id,amount,accepted_date, company_id , date ,
>>> patient_responsible_party_id patient_contact_responsible_party_id ,
>>> insurer_network_responsible_party_id, type, status)
>>> values (' || quote_ident(paymentId) || ',' || ...
>>>
>>> Something of that fashion.
>
>> Thanks for replying. So the '%' symbol can not be used with an EXECUTE
>> 'INSERT statement. I thought this was exactly what it was for?
>
> No - the RAISE statement takes that format, nothing else.
>
> However, you only need the EXECUTE for statements that you need planned
> every time they are called, e.g. can have their table-name changed. You
> should just be able to write:
> INSERT INTO ... VALUES (paymentId, 0.0, data.create_date ...)
>
> P.S. - you can remove much of a message when you reply, that way people
> can quickly follow the new parts of a message-thread.
>
> --
> Richard Huxton
> Archonet Ltd
>


Thanks Richard that worked for me.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match