[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('client_seq') INTO id;
INSERT INTO client (client_id, last_name, address1, country)
VALUES (id, $1, $2, $3);
INSERT INTO client_card (client_card_id, type, number, expiry_date,
client_id)
VALUES (nextval('client_card_seq'), $4, $5, $6, id);
RETURN id;
END;
' LANGUAGE 'plpgsql';
And the error message is
ERROR: parser: parse error at or near "client_seq"
EOF
Is this because of the ' ' ???
Also can you pass in a array or hash to the function?
Cheers
Graham
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 as two seperate calls in my perl scripts?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf
Of Yury Don
Sent: 18 August 2000 15:07
To: [EMAIL PROTECTED]
Subject: Re: [SQL] sequences in functions
Hello Graham,
Friday, August 18, 2000, 6:24:15 PM, you wrote:
GV> I am having problems referencing sequeces in a function, I think because
of
GV> the '' characters. The function I am creating is a follows: -
GV> CREATE FUNCTION InsertClient ( varchar, varchar, varchar, varchar,
varchar,
GV> varchar ) RETURNS int4 AS '
GV> DECLARE
GV> id INT;
GV> BEGIN
GV> SELECT nextval('client_seq') INTO id;
GV> INSERT INTO client (client_id, last_name, address1, country)
GV> VALUES (id, $1, $2, $3);
GV> INSERT INTO client_card (client_card_id, type, number,
expiry_date,
GV> client_id)
GV> VALUES (nextval('client_card_seq'), $4, $5, $6, id);
GV> RETURN id;
GV> END;
GV> ' LANGUAGE 'plpgsql';
GV> And the error message is
GV> ERROR: parser: parse error at or near "client_seq"
GV> EOF
GV> Is this because of the ' ' ???
You must to use two quotes:
SELECT nextval(''client_seq'') INTO id;
--
Best regards,
Yurymailto:[EMAIL PROTECTED]
[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 understanding was that if the default value is SQL NULL then any values passed into the function that are null would be treated as 'NULL'. This doesn't seem to be the case. Chances are I am overlooking something, could any one point me in the right direction? Cheers Graham
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 Hello, I'd like to return a set of integer in an pl/pgsql function. How can I do that ? I've tried things like that, put I've an error when executing : CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' DECLARE ID INTEGER; BEGIN select a into id from foo; return ID ; END; CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' DECLARE ID setof INTEGER; BEGIN select a into id from foo; return ID ; END; CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' BEGIN select a into id from foo; return select a from foo; END; Any help is welcomed. Thanks.
RE: [SQL] Null function parameters
Thanks Anatoly So if I understand you correctly you can't pass more than one NULL int into a function? Therefore Newbe DBA type question: - Is this a shortcoming in postgres or is it to be expected when dealing with transactions? If it is a shotcoming are there any plans to include it in future 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" == Tom Lane <[EMAIL PROTECTED]> writes: TL> "Graham Vickrage" <[EMAIL PROTECTED]> writes: >> However my understanding was that if the default value is SQL NULL then any >> values passed into the function that are null would be treated as 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. If you TL> declared the table column as NOT NULL then Postgres is doing exactly TL> what it should. You may wish to code the insert along the lines of TL> INSERT INTO table VALUES (..., COALESCE($1, suitable-default), ...) TL> COALESCE is a handy notation for "value1 unless it's NULL, in which case TL> value2". TL> regards, tom lane But unfortunately we have no answer for primary question: | Why if we pass to function ONLY ONE null agument all the oters| | argumenta in function's boby are null too?| | Or: is it possible to pass null arguments into plpgsql functions? | Example. create function a(int, int) returns int as ' begin raise notice ''1: % 2: %'', $1, $2; if $1 is null then return $2; end if; return $1; end; ' language 'plpgsql'; tolik=# select a(1,2); NOTICE: 1: 1 2: 2 a --- 1 (1 row) tolik=# select a(null,2); NOTICE: 1: 2: a --- (1 row) -- Anatoly K. Lasareff Email: [EMAIL PROTECTED]
[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
[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;
IF FOUND THEN
UPDATE order_detail SET cost_price = cost WHERE order_detail_id=$2;
RETURN cost;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
dvd=> select get_prod_cost_price ('DVD368', 10027);
get_prod_cost_price
---
9.81
(1 row)
Now I try and create the approprate trigger and I get the following:-
CREATE TRIGGER tg_update_order_detail AFTER insert
ON order_detail FOR EACH ROW
EXECUTE PROCEDURE get_prod_cost_price('product_id', 'order_detail_id');
ERROR: CreateTrigger: function get_prod_cost_price() does not exist
It is clear that it does exist so why does the trigger creation code not
find it?
Thanks in advance for any pointers.
Graham
[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, Has
> anyone had any similar problems/solutions or is it just that I am over
> looking something??? (I know there is an update but again when executed
> seperately it takes approx 1 sec)
>
> Regards
>
> Graham
>
> details as follows: -
>
> SELECT now(); SELECT count(*) FROM statistics WHERE url ='XXX' and
> website_id =1035; SELECT now();
>
> now
> --
> 2000-12-15 19:17:34+00
>
> count
> -
> 421
> (1 row)
>
> now
> --
> 2000-12-15 19:17:35+00
> (1 row)
>
> CREATE FUNCTION get_url_hits (varchar, int4) RETURNS int4 AS '
> DECLARE
> num INT4;
> BEGIN
> SELECT count(*) INTO num FROM statistics WHERE url = $1 and
> website_id = $2;
>
> IF num > 0 THEN
> UPDATE site_url SET hits = num, last_updated = now() where
> website_id = $2 and url = $1;
> END IF;
> RETURN num;
> END;' LANGUAGE 'plpgsql';
>
> select now(); select get_url_hits ('XXX', 1001); select now();
>
> now
> --
> 2000-12-15 19:21:40+00
> (1 row)
>
> get_url_hits
>
> 421
> (1 row)
>
> now
> --
> 2000-12-15 19:24:06+00
> (1 row)
>
>
>
>
>
>
winmail.dat
[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 me the associated line number in the script. This would be a very useful feature for me as my scripts can be very long. Is there a configuration option i am missing? Thanx in advance Graham winmail.dat
[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 as its doing a seqential scan then it may be possible to do it all at
once.
Has anyone done anything similar that or maybe could recommend a more
efficient solution.
Thanks
Graham
current select:
SELECT to_char(timestamp('01-Jun-2000'), 'DD-Mon-'),
to_char(timestamp('01-Aug-2000'), 'DD-Mon-'), count(*) FROM table WHERE
date >= timestamp('01-Jun-2000') AND date < timestamp('01-Aug-2000') AND
text = 'FOOBAR';
winmail.dat
[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? http://www.postgresql.org/search.mpl
[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=0.00..56174.49 rows=14976 width=88) Would anyone be able to tell me why it is seemingly infinite, i'm running linux, postgres v7.0 Cheers Graham winmail.dat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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 dealing with this problem as I ended up stopping and restarting the postmaster? Also are functions within functions dealt with in a 'transactional' sense? Cheers Graham winmail.dat ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[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), count(*)
FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND
status = 'Processing'
GROUP BY date_part('day', date);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[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_status' near line 19 parse error at or near "IF" I am running the following query against the function below: - Query: UPDATE supplier_order SET status = 'Pending' where id = 2003; CREATE FUNCTION change_sup_ord_status () RETURNS OPAQUE AS ' DECLARE num INT4; BEGIN IF OLD.status = ''Complete'' AND NEW.status != ''Complete'' THEN --Invalid option RAISE EXCEPTION ''This is an invlid status change ''; ELSIF OLD.status = ''Pending'' THEN IF NEW.status = ''Complete'' THEN UPDATE supplier_order_detail SET status=''Complete'' WHERE supplier_order_id = OLD.id AND status=''Pending''; ELSIF NEW.status = ''VOID'' OR NEW.status = ''Saved'' THEN SELECT count(*) INTO num FROM supplier_order_detail WHERE supplier_order_id = OLD.id AND status = ''Complete''; IF num > 0 THEN RAISE EXCEPTION ''Invalid change of status, some of the order has already been entered into stock''; END IF; END IF; ELSIF (OLD.status = ''VOID'') AND NEW.status = ''Complete'' THEN RAISE EXCEPTION ''Invalid change of status''; END IF; RETURN NEW; END;' LANGUAGE 'plpgsql'; Thanks in advance. Graham ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[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 ---(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
Re: [SQL] Copy user privileges
Thanks for the suggestions but as you guessed I do need to keep the old user and also groups would be unsuitable as I don't want either user to be able to see data on the other database. Thought there would be some SQL statement I could run on the sys tables but I don't know enough 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; [email protected] Subject: Re: [SQL] Copy user privileges On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom Lane wrote: > "Graham Vickrage" <[EMAIL PROTECTED]> writes: > > I want to keep all the privileges but assign them to a new user. > > > What is the best way of doing this? > > How about just renaming the old user to a new name? > > I don't think we have an ALTER command for that, but an UPDATE on > pg_shadow would get the job done just as well. What about ALTER USER RENAME TO? test=# CREATE USER user1; CREATE USER test=# CREATE TABLE foo (x integer); CREATE TABLE test=# GRANT SELECT ON foo TO user1; GRANT test=# \z foo Access privileges for database "test" Schema | Name | Type | Access privileges +--+---+-- public | foo | table | {postgres=arwdRxt/postgres,user1=r/postgres} (1 row) test=# ALTER USER user1 RENAME TO user2; ALTER USER test=# \z foo Access privileges for database "test" Schema | Name | Type | Access privileges +--+---+-- public | foo | table | {postgres=arwdRxt/postgres,user2=r/postgres} (1 row) Renaming the user is only useful if you no longer need the old user. If you need to keep the old user and copy its privileges, then consider granting privileges to groups instead of to users -- then you could just add the new user to a group. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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 "query buffer max length of 16384 exceeded" after each row. Would somebody please tell me how to increse this buffer (assuming this is whats required to solve the problem) as I have looked though the documentation and I am still struggling :-( Cheers Graham
