Re: [SQL] Delayed result from another connection
Dear Tom, Thanks for the explanation, it's more than enough. Some more questions if you don't mind: - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> Sent: Thursday, November 04, 2004 6:46 PM > =?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <[EMAIL PROTECTED]> writes: > > The php connects to the database and updates retval where id=seq. > > It seems to be OK, but the function returns the value of php_retval.retval > > _before_ the call. > > Yes, because your transaction is working with a database snapshot that > predates the other transaction run by the separate PHP connection. Yes, but the snapshot is not constant during a transaction, right? (as my example showed this clearly) ... > Prior to PG 8.0, new snapshots are not taken between commands of a function, > even in READ COMMITTED mode. You could get the behavior you want by > issuing separate interactive commands instead of wrapping the sequence > in a function. ... So if I understood you correctly, it's the function call that sees the old snapshot, and the transaction is more flexible in this issue. right? > > I thought it's something about "35.2 Visibility of Data Changes", but that's > > only for triggers, isn't it? > > Nope. I felt like it's not only for triggers but interpreted the doc as it'd be only for triggers... Having a closer look, discovered that there is another section with this title: "41.4 Visibility of Data Changes" ;) > This has been a sore spot for a long time, but we didn't get consensus > about changing it till recently ... I assume there is no point in begging for a backport... ;) Thanks again, G. %--- cut here ---% \end ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sql problem
Hi, I am having a problem with a system I developed using php with postgres. Take a look at this: Welcome to psql 7.3.2, the PostgreSQL interactive terminal. NetAdmin=# delete from operador where oplogin = 'ff'; ERROR: fk_historicosessao_operador referential integrity violation - key in operador still referenced from historicosessao and then, this: Welcome to psql 7.4.2, the PostgreSQL interactive terminal. NetAdmin=# delete from operador where oplogin = 'ff'; ERROR: update or delete on "operador" violates foreign key constraint "fk_historicosessao_operador" on "historicosessao" I am not able to get a error_number in pgsql errors, only the error message. So when I used pgsql 7.3 a used the string "referential integrity violation" to detect this error, but on version 7.4 of pgsql this string was replaced with "violates foreign key constraint" . Anyone has a definitive solution on this or the way is to wait and change all my code on future releases of pgsql? Thank you all for the attention. -- Att. Flavio Fonseca Administrador de Redes Divisao de Redes Universidade Federal de Uberlandia ---(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
[SQL] Help in stored procedure
Hi Guys, I need some help on building the following stored procedure, in PL/PgSQL. If this is not the right place to ask for help in this language, please let me know. Here is what I want to do, my comments in red: CREATE OR REPLACE FUNCTION discover_nsu(integer) RETURNS integer as 'DECLARE nsureturn integer; nsumax integer; caixaunitid alias for $1; branchid integer;BEGIN branchid := select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.caixaunit where t2.id = caixaunitid); -- the select above will return to me a result of one row and one column, with a integer variable inside, and will assign its result to branchid. nsumax := select max(nsu) from caixa.view_transacao_agencia_nsu where branch = branchid; -- here i'll use the var I discovered in the last select (branchid) and will do another select in a view (this view was previously created and works fine), and store the result of the query inside nsumax var. IF (nsumax <= 0) OR (nsumax ISNULL) THEN nsureturn:=0; ELSE nsureturn:=nsumax + 1; END IF; RETURN nsureturn; -- in the if-then-else above, i was just doing a simple test. If nsumax is equal or lower than 0, or nsumax is NULL, it'll assign 0 to the return var. Else, it'll get the max, add one, and assign the value to the return var, and finally, return it =) END' LANGUAGE 'plpgsql'; Okey, the function gets created fine b/c there are no sintax erros, the problem is when i try to execute: database=> select discover_nsu(1);ERROR: syntax error at or near "select" at character 9QUERY: SELECT select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.caixaunit where t2.id = $1 )CONTEXT: PL/pgSQL function "descobrir_nsu" line 7 at assignmentLINE 1: SELECT select t1.branch as result from caixa.caixaunit as t... ^ Well, the thing is: when I execute all the selects inside the stored procedure manually, they'll work, proving that there are no errors on the selects statements itself. I believe that the database cannot understand the type of the result, assuming that it's a row instead of a single record(??). I was looking at the PL/PgSQL reference manual and wasn't able to figure out a solution, so here I am .. can aonyone help me? Which type should I use to receive the return from the query? Are cast operations (for type conversions) supported in PL/PgSQL? Thanks for all, please help! Regards, Igor--[EMAIL PROTECTED]
Re: [SQL] Group by and aggregates
Franco Bruno Borghesi (franco) writes: > If I understand well, you want the highest cmup for each partno, that is > max(cmup) grouped by partno (only). > > SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS > max_cmup, sum(T.qty) AS sum_qty > FROM my_table T, (SELECT partno, max(cmup) AS max_cmup_for_partno FROM > my_table GROUP BY partno) AS TMP > WHERE tmp.partno=T.partno > GROUP BY T.partno, TMP.max_cmup_for_partno, T.status > > Hope it helped. This worked out nicely. Thank you very much ! /mich pgp9ccTsZYXoJ.pgp Description: PGP signature
Re: [SQL] Help in stored procedure
I think you want to be using SELECT INTO rather than assignment for your queries. See http://www.postgresql.org/docs/7.4/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT http://www.postgresql.org/docs/7.4/static/plpgsql- statements.html#PLPGSQL-SELECT-INTO The expression part of a basic assignment in PL/PgSQL is sent to be executed in a SELECT, so you're basically saying "SELECT select"... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 5, 2004, at 8:35 AM, Igor Maciel Macaubas wrote: Hi Guys, I need some help on building the following stored procedure, in PL/PgSQL. If this is not the right place to ask for help in this language, please let me know. Here is what I want to do, my comments in red: CREATE OR REPLACE FUNCTION discover_nsu(integer) RETURNS integer as ' DECLARE nsureturn integer; nsumax integer; caixaunitid alias for $1; branchid integer; BEGIN branchid := select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.caixaunit where t2.id = caixaunitid); -- the select above will return to me a result of one row and one column, with a integer variable inside, and will assign its result to branchid. nsumax := select max(nsu) from caixa.view_transacao_agencia_nsu where branch = branchid; -- here i'll use the var I discovered in the last select (branchid) and will do another select in a view (this view was previously created and works fine), and store the result of the query inside nsumax var. IF (nsumax <= 0) OR (nsumax ISNULL) THEN nsureturn:=0; ELSE nsureturn:=nsumax + 1; END IF; RETURN nsureturn; -- in the if-then-else above, i was just doing a simple test. If nsumax is equal or lower than 0, or nsumax is NULL, it'll assign 0 to the return var. Else, it'll get the max, add one, and assign the value to the return var, and finally, return it =) END ' LANGUAGE 'plpgsql'; Okey, the function gets created fine b/c there are no sintax erros, the problem is when i try to execute: database=> select discover_nsu(1); ERROR: syntax error at or near "select" at character 9 QUERY: SELECT select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.cai xaunit where t2.id = $1 ) CONTEXT: PL/pgSQL function "descobrir_nsu" line 7 at assignment LINE 1: SELECT select t1.branch as result from caixa.caixaunit as t... ^ Well, the thing is: when I execute all the selects inside the stored procedure manually, they'll work, proving that there are no errors on the selects statements itself. I believe that the database cannot understand the type of the result, assuming that it's a row instead of a single record(??). I was looking at the PL/PgSQL reference manual and wasn't able to figure out a solution, so here I am .. can aonyone help me? Which type should I use to receive the return from the query? Are cast operations (for type conversions) supported in PL/PgSQL? Thanks for all, please help! Regards, Igor -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] oracle v$session equivalent in postgresql
I’m looking for the equivalent in postgresql to the
v$session variables in Oracle. In particular, I need to convert the following
statement:
select s.program, s.machine into
:New.app_name,:New.comp_name from v$session s where s.audsid=userenv('SESSIONID');
Any help appreciated
Re: [SQL] Delayed result from another connection
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <[EMAIL PROTECTED]> writes: > From: "Tom Lane" <[EMAIL PROTECTED]> >> Yes, because your transaction is working with a database snapshot that >> predates the other transaction run by the separate PHP connection. > Yes, but the snapshot is not constant during a transaction, right? In pre-8.0 releases, the snapshot only advances between interactive commands, not between commands of a function; so you can't see the results of other transactions that commit after the function starts. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sql problem
O Flavio Fonseca έγραψε στις Nov 5, 2004 : > Hi, > > I am having a problem with a system I developed using php with postgres. > > Take a look at this: > > Welcome to psql 7.3.2, the PostgreSQL interactive terminal. > NetAdmin=# delete from operador where oplogin = 'ff'; > ERROR: fk_historicosessao_operador referential integrity violation - key in > operador still referenced from historicosessao > > and then, this: > > Welcome to psql 7.4.2, the PostgreSQL interactive terminal. > NetAdmin=# delete from operador where oplogin = 'ff'; > ERROR: update or delete on "operador" violates foreign key constraint > "fk_historicosessao_operador" on "historicosessao" > > I am not able to get a error_number in pgsql errors, only the error message. > So when I used pgsql 7.3 a used the string "referential integrity violation" > to detect this error, but on version 7.4 of pgsql this string was replaced > with "violates foreign key constraint" . > Anyone has a definitive solution on this or the way is to wait and change all > my code on future releases of pgsql? The right way to do so is to query for the SQLSTATE codes. For instance the jdbc7.4.6 driver supports sqlstate, which you can retrieve by sqle.getSQLState() That text of an Error may change across releases but the error code should be constant. p.s I dont do it my self. i just let my users educate themselves :) > > > Thank you all for the attention. > -- -Achilleus ---(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
[SQL] Simple SQL Question
Hi All! In one of my application, I would like to implement incremental fetching. The problem is that I would like to run it from a stateless application server, so I have to tell where to begin fetching data. It is simple for a table which has single column primary key, but causes difficulties (at least for me) in a more-column primary key.. Let say I have a table wich has the primary key: itemkey,location table1 -- itemkey location ... select * from table1 LIMIT x gives me the first x row of the result. After that, I save the last value, and next time, I adjust the query as select * from table1 where itemkey>:lastvalue LIMIT x that should be enough for an 'Item' table, but I have another column in the primary key. let say, I have the following in Table1: itemkey location 1 1 1 2 ... 5 1 5 2 5 3 <--- lets say this is the last value next time i want to run a query, which starts from 5 4 6 5 and so on.. How can I specify that in sql? I dont want to use cursor:), I would like to do it in plain sql. (It it is possible). Thank you in advance Andras Kutrovics ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Simple SQL Question
wouldn't it be easier using offset & limit?: you always select from the table with an itemkey,location order by clause. You save the current offset between requests, and for every request you re-execute the query with a different offset. If you still want to go with what you already have, you should keep the lastItemKey and lastLocaltion values between requests, and your where clause should be something like: WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR (itemKey>:lastItemKey) hope it helps. On Fri, 2004-11-05 at 13:54, Andras Kutrovics wrote: Hi All! In one of my application, I would like to implement incremental fetching. The problem is that I would like to run it from a stateless application server, so I have to tell where to begin fetching data. It is simple for a table which has single column primary key, but causes difficulties (at least for me) in a more-column primary key.. Let say I have a table wich has the primary key: itemkey,location table1 -- itemkey location ... select * from table1 LIMIT x gives me the first x row of the result. After that, I save the last value, and next time, I adjust the query as select * from table1 where itemkey>:lastvalue LIMIT x that should be enough for an 'Item' table, but I have another column in the primary key. let say, I have the following in Table1: itemkey location 1 1 1 2 ... 5 1 5 2 5 3 <--- lets say this is the last value next time i want to run a query, which starts from 5 4 6 5 and so on.. How can I specify that in sql? I dont want to use cursor:), I would like to do it in plain sql. (It it is possible). Thank you in advance Andras Kutrovics ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [SQL] Simple SQL Question
Hi All! In one of my application, I would like to implement incremental fetching. The problem is that I would like to run it from a stateless application server, so I have to tell where to begin fetching data. It is simple for a table which has single column primary key, but causes difficulties (at least for me) in a more-column primary key.. Let say I have a table wich has the primary key: itemkey,location table1 -- itemkey location ... select * from table1 LIMIT x gives me the first x row of the result. After that, I save the last value, and next time, I adjust the query as select * from table1 where itemkey>:lastvalue LIMIT x Why do you complicate it so much? Everything you need is: select * from table1 LIMIT x select * from table1 LIMIT x OFFSET x select * from table1 LIMIT x OFFSET 2*x Remember to sort rows before using limit/offset. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] oracle v$session equivalent in postgresql
On Fri, 5 Nov 2004 08:27:58 -0700, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
>
> I'm looking for the equivalent in postgresql to the v$session variables in
> Oracle. In particular, I need to convert the following statement:
>
> select s.program, s.machine into :New.app_name,:New.comp_name from v$session
> s where s.audsid=userenv('SESSIONID');
You want basically this information then:
SQL> select s.program, s.machine from v$session s where
s.audsid=userenv('SESSIONID');
PROGRAM
MACHINE
[EMAIL PROTECTED] (TNS V1-V3)
linux
There's no direct equivalent of v$session in PostgreSQL, and I don't
know any way of determining the client program name (AFAIK). More
information about monitoring user activity can be found e.g. here:
http://www.postgresql.org/docs/current/static/monitoring.html
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
