[SQL] Grant permission to all objects of user A to user B
Dear Friends, Postgres 7.3.2. I have an database with owner USRA who owns about 300 objects (tables and functions). Now I want to give 'ALL' permission to all the objects of USRA to another database user USRB. If I use the grant i need to type all the objects as comma separated, like the following booktown=# GRANT ALL ON customers, books, editions, publishers booktown-# TO manager; CHANGEIs there any other way do it.ThanksKumar
[SQL] problem porting MySQL SQL to Postgres
I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECT DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE (DEWEY_HUNDREDS = 9) AND (DEWEY_TENS = 0) AND (DEWEY_TENS = 9) AND (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND (DEWEY_TYPE = 't') AND (DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. Any help would be much appreciated TIA -- Dan Field ---(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
Re: [SQL] problem porting MySQL SQL to Postgres
On Thu, Apr 15, 2004 at 11:25:59AM +0100, Dan Field wrote: I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECT DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS,DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE (DEWEY_HUNDREDS = 9) AND (DEWEY_TENS = 0) AND (DEWEY_TENS = 9) AND (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND please use 'IS NULL' instead of '= NULL' (DEWEY_TYPE = 't') AND (DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. can you post the output of '\d lu_dewey'? -- __ Nothing is as subjective as reality Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(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
Re: [SQL] problem porting MySQL SQL to Postgres
On 15/04/2004 11:25 Dan Field wrote: I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECT DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE (DEWEY_HUNDREDS = 9) AND (DEWEY_TENS = 0) AND (DEWEY_TENS = 9) AND (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND (DEWEY_TYPE = 't') AND (DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. Any help would be much appreciated You're trying to use the string concatenation operator (||) in a boolean test.. That's an invalid comparison according to the SQL specs. You need to use the SQL OR operator e.e., (DEWEY_POINT_TENS = 0 OR DEWEY_POINT_TENS = NULL) AND ~~ Your = NULL tests are also not valid SQL (should be IS NULL). MySQL does not follow the specs in a number or areas. PostgreSQL is very standards-complient. Write valid SQL and you should be OK. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problem porting MySQL SQL to Postgres
El Jue 15 Abr 2004 07:25, Dan Field escribió: I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECT DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS,DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE (DEWEY_HUNDREDS = 9) AND (DEWEY_TENS = 0) AND (DEWEY_TENS = 9) AND (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND (DEWEY_TYPE = 't') AND (DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. 1) Change the = NULL to IS NULL which is how it's defined in the SQL standards. 2) What data type does DEWEY_TYPE have? -- 08:28:01 up 37 days, 12:55, 2 users, load average: 0.72, 0.77, 0.90 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] function returning array
Tom Lane writes:
Dennis [EMAIL PROTECTED] writes:
I am trying to return an array from a function and don't seem to be having
luck.
Seems to work for me ... what PG version are you using?
pg 7.4.1
I should have listed the source for the function. Here is a simplified
parseString function and the foo that calls it.
dennis=# create or replace function parseString (varchar, varchar)
dennis-# RETURNS varchar[] AS '
dennis'# DECLARE
dennis'# pParsed varchar[];
dennis'# pString ALIAS FOR $1;
dennis'# pSeparator ALIAS FOR $2;
dennis'# begin
dennis'#
dennis'# -- example simplified
dennis'# pParsed[1] = ''blah'';
dennis'# pParsed[2] = ''hrmmph'';
dennis'#
dennis'# return pParsed;
dennis'# END;
dennis'# ' language plpgsql;
CREATE FUNCTION
dennis=#
dennis=# create or replace function foo() returns varchar as '
dennis'# declare
dennis'#results varchar[];
dennis'#tmpv varchar;
dennis'# begin
dennis'#results := parseString(''asdf'',''asdf'');
dennis'#tmpv := results[1];
dennis'#RAISE NOTICE '' tmpv = % '',tmpv;
dennis'#return tmpv;
dennis'# end' language plpgsql;
CREATE FUNCTION
dennis=# select foo();
NOTICE: tmpv = NULL
foo
-
(1 row)
Dennis
[EMAIL PROTECTED]
---(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
Re: [SQL] cursors and for loops?
Just FYI, recent versions of PG use cursors internally for PL/pgSQL FOR loops. So there is no danger for a procedure to run out of memory when looping over a huge result set ... at least not because of that. Jan Dennis wrote: Tom Lane writes: Something like LOOP FETCH ...; EXIT WHEN NOT found; ... END LOOP; Thank you! I tried finding documentation on found in this context and didn't come up with anything. Can you point me to where it is documented? Also, I am not getting the results I think I should be getting. Is there any kind of debug setting, or if not that, a way to output text (i.e. printf) from plpgsql? Thanks, Dennis [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Row count after SELECT INTO?
Dear SQL and plPgSQL gurus, I seek for your wisdom. I have a variable assignment via SELECT INTO in my function, and I want to separate 3 cases: 1) no rows found; 2) one row found; 3) many rows found (ambiguous select). The first case is simple, I use FOUND variable for it. But how about the third? I tried to GET DIAGNOSTICS nrows = ROW_COUNT, bit it seems to get only value of 0 or 1. Is this how it supposed to be when SELECTing INTO a single variable? The only way I see now is a FOR ... IN SELECT loop, and I woner if there is a simpler solution. Could you please help me? -- Fduch M. Pravking I think ROW_COUNT values of 0/1 when SELECTing INTO a single variable are correct. Because you cannot see more than one row anyway. If you simply want to know about the number of result rows and don't care about the data, you may change your SELECT ... into a SELECT COUNT(... and separate your 3 cases from there. Otherwise you'll have to go to a FOR ... IN SELECT loop. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] Grant permission to all objects of user A to user B
Dear Friends, Postgres 7.3.2. I have an database with owner USRA who owns about 300 objects (tables and f= unctions). Now I want to give 'ALL' permission to all the objects of USRA t= o another database user USRB. If I use the grant i need to type all the objects as comma separated, like = the following booktown=3D# GRANT ALL ON customers, books, editions, publishers booktown-# TO manager; CHANGE Is there any other way do it.ThanksKumar If you're using schemas you can grant the whole schema to the new user. If not, the only alternative I know about is extract the table/function names via \dt / \df commands within psql and use a script to grant the privileges. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] function returning array
Dennis wrote:
pg 7.4.1
I should have listed the source for the function. Here is a simplified
parseString function and the foo that calls it.
dennis=# create or replace function parseString (varchar, varchar)
dennis-# RETURNS varchar[] AS '
dennis'# DECLARE
dennis'# pParsed varchar[];
Make that last line:
pParsed varchar[] := ''{}'';
That initializes pParsed to an *empty* array. Otherwise pParsed is NULL,
and when you attempt to extend a NULL array, e.g. pParsed[1] =
''blah'' you still get NULL. It is similar to this:
regression=# select NULL || 'blah';
?column?
--
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] function returning array
Joe Conway writes:
dennis'# pParsed varchar[];
Make that last line:
pParsed varchar[] := ''{}'';
That initializes pParsed to an *empty* array. Otherwise pParsed is NULL,
and when you attempt to extend a NULL array, e.g. pParsed[1] = ''blah''
you still get NULL.
Joe, thank you very much! It makes much sense now that you tell me, but it
was obviously not something I thought of.
Dennis
pg-user a t calico-consulting dot com
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Update is very slow on a bigger table
hi, i'm running an update procedure on a table with approximately 100,000 records. The problem is the procedure is running endlessly even when the update is very simple like in the following example: update mytable set client_id = 1 where mytable_id = 1 mytable_id is the primary key of mytable. the process under which the update is running reports among the usual process data UPDATE waiting. I'm using PostgreSQL 7.3.4 on SuSE 9.0. From what I have seen it doesn't look like that the host computer has problem with the resources. Any idea why is this happening? thanks, dimitar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] Update is very slow on a bigger table
Dimitar Georgievski [EMAIL PROTECTED] writes: the process under which the update is running reports among the usual process data UPDATE waiting. I don't think the UPDATE is doing anything at all --- that ps output implies that it's waiting for some other backend to release an exclusive lock on the table, or an update lock on one of the rows to be updated. The pg_locks view might help you determine who's the culprit. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] SQL script
On Thursday 15 April 2004 09:22, [EMAIL PROTECTED] wrote: good day toall.. imm running postgreSQL on linux. my main goal is to create a script that will dump a data from our server too my local PC. the content of the script would be execute sql command in one shot: 1. dump data 2. update and insert some rows to selected table. how can i accommplish this task and also how can i run this script? is there any one ccan help me. any idea will appreciate very much. How are you connecting from client = server? Do you just want a pg_dump style dump, a specific table/query? Do you need to provide values for this update/insert, or is it fixed (updating timestamps sort of thing). -- Richard Huxton Archonet Ltd ---(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
