[SQL] SELECT INTO returning more than one row
Hi. I have this problem in a plpgsql function: SELECT INTO myvar col FROM table WHERE ...; IF THEN do something ELSE IF THEN do something else ELSE do other things If I know that myvar IS NULL OR GET DIAGNOSTICS ROW_COUNT is zero. Is there a way to know if the query returned more than one row? In Oracle PL/SQL I can catch the TOO_MANY_ROWS exception but in Postgres I found no way for doing it: - myvar is correctly valued with the first occurrence of the result - FOUND is TRUE - GET DIAGNOSTICS ROW_COUNT is 1 (it counts only fetched rows) I tried to use a cursor but the only way to know how many rows it returned is to fetch them all. (And I don't like this solution) Thanks in advance. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER records based on parameters in IN clause
Riya Verghese wrote: select * from table where id IN (2003,1342,799, 1450) I would like the records to be ordered as 2003, 1342, 799, 1450. The outer query has no knowledge of the count(id) that the inner_query is ordering by. I think this is the real problem: outer query must know count(id) to order by count(id). You can use it in the outer with something like this: SELECT table.* FROM table JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x ORDER BY x.count Bye. ---(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] ORDER records based on parameters in IN clause
SELECT table.* FROM table JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x ORDER BY x.count Bye. Sorry: I forgot join condition: SELECT table.* FROM table JOIN (SELECT id, count(id) AS count FROM... your subquery) AS x ON (table.id = x.id) ORDER BY x.count ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help on Procedure running external function
Din Adrian wrote: Hello, I have a 'big' problem: I am trying to run from a procedure a function witch generate a new document number (max from table +1 ) and after to insert a document with this number, but the function returns me the same number each time because the tranzaction is not finished and the inserts are not commited and of course the next document number is the same. ... for ... loop nr=get_me_next_number(params); insert into table values (nr,...) end loop ... ... error inserting in table .. primary_key nr . Is any way in making the external function to 'know' that I inserted another row but this insert is in a tranzaction that is not finish yet ? ar onother solution ? for now I 'solved' by asking for a nr once and generate myself next number (+1) but this is not a correct solution (in this time somebody else could insert a document with the same nr as the procedure ) thank you, Adi I think the best solution is to use a sequence, not "select max(id) +1 from table". Look here: http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html Bye ---(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] Help on Procedure running external function
Din Adrian wrote: Yes , I know what sequence is, but our procedure for generating doc numbers is v. big and has manny (4) parameters and we did'nt use sequence in it for this reason any other advice ? I think there is no way to have any information about non committed transactions. I don't know if I understand well your problem but from what I see "get_me_next_number" function runs in a different transaction (Why? Is it an externale procedure that make its own connection to the DB?)), otherwise it would see the new inserted number. The better solution is to run "get_me_next_number" in the same transaction. Another solution (not so good but should work) is to generate by yourself the number (as you do) and lock the table until you end to prevent others inserting documents. I hope this helps you. Bye thak you, Adi On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote: Din Adrian wrote: Hello, I have a 'big' problem: I am trying to run from a procedure a function witch generate a new document number (max from table +1 ) and after to insert a document with this number, but the function returns me the same number each time because the tranzaction is not finished and the inserts are not commited and of course the next document number is the same. ... for ... loop nr=get_me_next_number(params); insert into table values (nr,...) end loop ... ... error inserting in table .. primary_key nr . Is any way in making the external function to 'know' that I inserted another row but this insert is in a tranzaction that is not finish yet ? ar onother solution ? for now I 'solved' by asking for a nr once and generate myself next number (+1) but this is not a correct solution (in this time somebody else could insert a document with the same nr as the procedure ) thank you, Adi I think the best solution is to use a sequence, not "select max(id) +1 from table". Look here: http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html Bye ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help on Procedure running external function
I think you should use 'FOR UPDATE' clause in your first "select":
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare FOR UPDATE;
In this way you lock the rows eventually returned and no one can update
them (or select them "for update") until your transaction finished.
Is this good for you?
However IMHO you should think your procedures to use sequences...
1. the function get_me_next_number is runing from this procedure (same
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store each
maxnrdoc value - but the temp table give me sometimes a relation with
OID # does not exist - problem that I can only solve by using
only execute - but I don't think I can ...?! :))
here is the example:
create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare;
if vnrbon is null
then
select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
insert into MagMaxNrBon values
(dsgroup.magazie_implicita_lansare,vNrBon);
else
update MagMaxNrBon set maxnrbon=vNrBon where magazie =
dsgroup.magazie_implicita_lansare;
end if;
...
and in this way vNrBon is correct one ... I will try to use oly execute
on insert,update and select on temp table MagMaxNrBon
(o_gen_calc_nr_doc is the "get_me_next_number" function)
thank you,
Adria Din
On Mon, 04 Jul 2005 17:27:20 +0200, Zac <[EMAIL PROTECTED]> wrote:
Din Adrian wrote:
Yes , I know what sequence is, but our procedure for generating
doc numbers is v. big and has manny (4) parameters and we did'nt
use sequence in it for this reason
any other advice ?
I think there is no way to have any information about non committed
transactions.
I don't know if I understand well your problem but from what I see
"get_me_next_number" function runs in a different transaction (Why?
Is it an externale procedure that make its own connection to the
DB?)), otherwise it would see the new inserted number. The better
solution is to run "get_me_next_number" in the same transaction.
Another solution (not so good but should work) is to generate by
yourself the number (as you do) and lock the table until you end to
prevent others inserting documents.
I hope this helps you.
Bye
thak you,
Adi
On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote:
Din Adrian wrote:
Hello,
I have a 'big' problem:
I am trying to run from a procedure a function witch generate a
newdocument number (max from table +1 ) and after to insert a
document with this number, but the function returns me the same
number each time because the tranzaction is not finished and the
inserts are not commited and of course the next document number
is the same.
...
for ...
loop
nr=get_me_next_number(params);
insert into table values (nr,...)
end loop
...
...
error inserting in table .. primary_key nr .
Is any way in making the external function to 'know' that I
inserted another row but this insert is in a tranzaction that is
not finish yet ? ar onother solution ?
for now I 'solved' by asking for a nr once and generate myself
next number (+1) but this is not a correct solution (in this
time somebody else could insert a document with the same nr as
the procedure )
thank you,
Adi
I think the best solution is to use a sequence, not "select max(id)
+1 from table". Look here:
http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Bye
---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to join several selects
Josep Sanmartí wrote:
Hello,
I have a 'big' problem:
I have the following table users(name, start_time, end_time), a new row
is set whenever a user logs into a server. I want to know how many
users have logged in EVERYDAY between 2 different dates. The only idea
that I have is making several select (one for each day):
SELECT COUNT(name) FROM users WHERE start_time between "startDate"
and "startDate+1"
SELECT COUNT(name) FROM users WHERE start_time between "startDate+1"
and "startDate+2"
...
I would like to know if its possible to make it in 1 sql statement or
just which is the best efficient way to solve it.
By the way, I use Postgres 7.4.
Thanks!
SELECT
date_trunc('day', start_time) as day, count(name)
FROM
users
WHERE
start_time between "startDate" AND "endDate"
GROUP BY
day;
---(end of broadcast)---
TIP 1: 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
Re: [SQL] psql commandline
Be quiet... it is not a db problem: your shell interprets "'" characters before sending them to psql so your query becomes UPDATE users SET pin=12345 WHERE login=admin; and column admin (not the literal 'admin') doesn't really exist! Try this: su - postgres -c "psql --dbname database --command \"UPDATE users SET pin=12345 WHERE login='admin';\"" The error is : column "admin" doesn`t exist What the f... is that db doing? --- Ursprüngliche Nachricht --- Von: Kenneth Gonsalves <[EMAIL PROTECTED]> An: pgsql-sql@postgresql.org Betreff: Re: [SQL] psql commandline Datum: Wed, 31 Aug 2005 18:13:18 +0530 On Wednesday 31 Aug 2005 6:00 pm, dIGITx wrote: su - postgres -c 'psql --dbname database --command "UPDATE users SET pin=12345 WHERE login='admin';"' works for me - what is the error you are getting? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡà³�ಲಿನಕà³�ಸ வாழà¯�க! ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
