Re: [SQL] Copy user privileges

about the internals to attempt it. Thanks again, Graham -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: 12 July 2005 20:29 To: Tom Lane Cc: Graham Vickrage; pgsql-sql@postgresql.org Subject: Re: [SQL] Copy user privileges On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom

[SQL] Copy user privileges

Hi All, I have dumped a database with no data and restored it with a new db name. I want to keep all the privileges but assign them to a new user. What is the best way of doing this? Is it to alter the system tables directly in which case which ones. TIA, Graham ---(

[SQL] Scheme not dropping

I am dropping a database with an additional scheme other than public on version 7.3.2. When I come to recreate the database with the same scheme it gives me the error: ERROR: namespace "xxx" already exists Is this temp table issue, if so how do I get round it? Many thanks in advance. Graham

[SQL] Conversion question

Apologies as this probably isn't really for this list but... In postgresql you can execute a statement such as: SELECT 1 > 2; And it would return 'f' Does anyone know if you can do this in SQL Server as I have to do a conversion of some prewritten SQL code. MTIA, Graham.

[SQL] How do I view triggers

Hi All, I am slightly confused as to how I view what triggers I have on a certain table. select * from pg_trigger doesn't show me the trigger I have just created, but its definitely there because when I try and create it, it gives an error that it already exists. TIA Graham --

[SQL] pg_func problem

Hi All, I am trying to use the function below, it works fine on my dev server running 7.2.3 but does not work on my production server running 7.1.2. (both on linux) Would anyone be able to shed some light on why this is the case. The error I get is: ERROR during compile of 'change_sup_ord_statu

[SQL] Group by date_part

Hi, I need to select the amount of orders per day from an order table. The statement I have only selects the count if there is at least 1 order for a particular day, which make sense. I however need a count of 0 for days that don't have any. Can anyone help? SQL: SELECT date_part('day', date)

[SQL] Strategy for unlocking query

I have just done a rather large transaction via a telnet/psql session which executed OK. The problem occured when the telnet session timed out before I could commit the rows. This must have locked the rows in question because when I tried to vacuum the table it just hung. What is the best way of

[SQL] Update taking forever

I am trying to do an update on column in a table with 1.5 millions rows. The SQL is as follows, I am also putting it in a transaction in case things go wrong. begin; update statistics set parameters = NULL where parameters =''; An explain produces the following: - Seq Scan on statistics (cost=

[SQL] Killing Postmaster

Hi All, What is the correct way of killing postgres 7.0 on redhat linux. Is there a reason why vacuum hangs on a DB with about 1.5 million rows? Cheers Graham winmail.dat ---(end of broadcast)--- TIP 6: Have you searched our list archives?

[SQL] grouping by date increments

I am trying to write a select statement to count the occurences of a particular string between a set of dates. I have written this successfully but need to get the count in time increments such as per day/week/month. At the moment I am doing a select for each increment seperately but figure that

[SQL] psql -f option

I am trying to use the psql -f option to load a script into the DB ( v7.0 ) from the linux command line. The documentation says -f enables some nice features such as error messages with line numbers. It seems to me that this is half true i.e. it shows me error messages, its doesn't however give

[SQL] Use of indexes in plpgsql functions

> I have a table with 650k rows with an index on URL (pg v7.0.0 on > i686-pc-linux-gnu) > > When using psql the select query behaves as expected i.e. takes < 1 second > (and explain tells me it is using the correct index) > > However when I put this into a pl function it takes about 2.5 mins, H

[SQL] Trigger cant find function

I seem to be having difficulty creating a trigger. I have creted the function and tested it which seems to work fine: - CREATE FUNCTION get_prod_cost_price (varchar, int8) RETURNS float AS ' DECLARE cost FLOAT; BEGIN SELECT cost_price INTO cost FROM product WHERE code = $1;

[SQL] Return from stored procedures

Probably a very simple question, but how do you define a function that returns the sucess of an insert or update in a function i.e. CREATE FUNCTION foo ( varchar, int8 ) RETURNS bool AS 'UPDATE table WHERE something' LANGUAGE 'sql'; Cheers Graham

RE: [SQL] Null function parameters

uture releases? Regards Graham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anatoly K. Lasareff Sent: 23 August 2000 12:46 To: Tom Lane Cc: Graham Vickrage; postgresql Subject: Re: [SQL] Null function parameters >>>>> "TL"

RE: [SQL] Using SETOF in plpgsql function

As far as i know, you can only return single values from functions at the moment. Regards Graham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of hlefebvre Sent: 23 August 2000 11:08 To: [EMAIL PROTECTED] Subject: [SQL] Using SETOF in plpgsql function

[SQL] Null function parameters

Hi All, I am trying to create a function that takes an int as its param and insert the value into a table. The problem occurs when the value passed is NULL, the error message returned is - Execute failed ERROR: ExecAppend: Fail to add null value in not null attribute type However my understand

RE: [SQL] sequences in functions

I have noticed that you can only pass 16 parameters to a function, I was therefore wondering how you can do atomic inserts (such as the function below but with more params) using pl/pgsql if you can't pass complex data types. Is this something that transactions are not used for or is it best done

[SQL] sequences in functions

I am having problems referencing sequeces in a function, I think because of the '' characters. The function I am creating is a follows: - CREATE FUNCTION InsertClient ( varchar, varchar, varchar, varchar, varchar, varchar ) RETURNS int4 AS ' DECLARE id INT; BEGIN SELECT nextval('c

[SQL] pg_dump problem

I am trying to backup a production database running on v6.5 and restore it on our test machine running v6.5. The largest table has about 750,000 rows, the other 5 tables are very small approx 100 rows. When I try to restore the database using "psql -e database < db.out" I get the error message