Re: [SQL] How do you write this query?
Thank goodness for nested select! select data1 from test where data2 = ( select distinct data2 from test where data1 = 'pooh') and data = 3; JLL Richard Huxton wrote: On Thursday 31 Oct 2002 6:21 pm, Wei Weng wrote: data | data1 | data2 --+---+--- 1 | foo | bar 2 | greg | bar 3 | pooh | bar 4 | dah | peng I need a query that returns me the data1 that satisfies the logic of the following pseudo code: 1: select data2 into @out from test where data1 = 'pooh' 2: select data1 from test where data2 = @out and data = 3 The most literal would be something like: SELECT t1.data1 FROM test t1 WHERE t1.data=3 AND t1.data2 IN (SELECT t2.data2 FROM test t2 WHERE t2.data1='pooh') You can probably get away without the t1/t2 stuff but that should make things clear. Since Postgresql isn't very good at optimising IN, you might want to rewrite it as an EXISTS query instead - see the manuals and mailing list archives for details. HTH -- Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] psql history
On Mon, 2002-10-28 at 14:57, [EMAIL PROTECTED] wrote: Hi everibody, i have installed Postgres 7.2.2 from a tarball, but using psql i can not have the history of the last command. When i used Postgres from rpm this useful element worked very well! Why that? I didn't see any other response to this, but your problem is that for some reason when you built the file from the tarball, it didn't pick up on your readline utilities. You might want to check your configure output, it might hold a clue as to what went wrong. If not, do a search on the archives (probably the general list would be more fruitful) as this comes up quite often. Robert Treat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Returning a recordset and filling datatable in a .NET application
Hi,
I have a problem with using .NET and PostgreSQL. In a previous thread called
I noticed that it is possible to use and create functions that return
tuples or RecordSets. I now want to use them in .NET.
I followed the instructions which are available at :
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html these
work in the standard psql client :
I created the function using the following :
CREATE FUNCTION public.p0012_fetch_supplier(varchar, varchar, refcursor)
RETURNS refcursor AS '
BEGIN
OPEN $3 FOR SELECT * FROM SUPP_T WHERE CODE = $1 AND DSRCODE =
$2;
RETURN $3;
END;
' LANGUAGE 'plpgsql' VOLATILE;
Then I did these in psql :
SBA=# BEGIN;
BEGIN
SBA=# SELECT p0012_fetch_supplier('1','1','funccursor');
p0012_fetch_supplier
--
funccursor
(1 row)
SBA=# FETCH ALL IN funccursor;
DSRCODE | CODE
1 | 1
(1 row)
SBA=# COMMIT;
COMMIT
The results tell me that the function works. (whoohoo)
But when I do the same thing in a .Net application, I get an empty DataTable
(row count = 0)
This is what I do in .Net 5and I do know that most of you people dislike
.NET and actually .. that is not the issue for me :), I just want this to
work because we are going to need this for our application)
Dim CN As New Microsoft.Data.Odbc.OdbcConnection(DSN=PostgreSQL30)
Dim CM As New Microsoft.Data.Odbc.OdbcCommand(BEGIN; SELECT
p0012_fetch_supplier('1','1','funccursor'); FETCH ALL IN
funccursor; COMMIT;, CN)
Dim DA As New Microsoft.Data.Odbc.OdbcDataAdapter(CM)
Dim DT As New DataTable()
Try
CM.CommandType = CommandType.Text
DA.SelectCommand.Connection.Open()
DA.Fill(DT)
DA.SelectCommand.Connection.Close()
Catch ex As Microsoft.Data.Odbc.OdbcException
Debug.WriteLine(ex.Message)
Debug.WriteLine(ex.Source)
Debug.WriteLine(ex.HelpLink)
Finally
CN.Close()
It does not raise an exception so there are no real 'errors'; it just does
not give 'data' to the ADO.NET container.
If I am asking this in the wrong mailinglist, then please point me in the
right direction. I don't think that I will get a answer at Microsoft.com so
that is why I ask it here ..
Thx in advance
Jonas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Returning a recordset and filling datatable in a .NET
On Thu, 31 Oct 2002, Jonas Wouters wrote:
Hi altho i doubt anybody here has any .net experience,
i'll give my bet.
try to place your sql commands in such a way the the
FETCH ALL command is the last in the stream,
(that is get rid of begin,commit statements)
Hi,
I have a problem with using .NET and PostgreSQL. In a previous thread called
I noticed that it is possible to use and create functions that return
tuples or RecordSets. I now want to use them in .NET.
I followed the instructions which are available at :
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html these
work in the standard psql client :
I created the function using the following :
CREATE FUNCTION public.p0012_fetch_supplier(varchar, varchar, refcursor)
RETURNS refcursor AS '
BEGIN
OPEN $3 FOR SELECT * FROM SUPP_T WHERE CODE = $1 AND DSRCODE =
$2;
RETURN $3;
END;
' LANGUAGE 'plpgsql' VOLATILE;
Then I did these in psql :
SBA=# BEGIN;
BEGIN
SBA=# SELECT p0012_fetch_supplier('1','1','funccursor');
p0012_fetch_supplier
--
funccursor
(1 row)
SBA=# FETCH ALL IN funccursor;
DSRCODE | CODE
1 | 1
(1 row)
SBA=# COMMIT;
COMMIT
The results tell me that the function works. (whoohoo)
But when I do the same thing in a .Net application, I get an empty DataTable
(row count = 0)
This is what I do in .Net 5and I do know that most of you people dislike
.NET and actually .. that is not the issue for me :), I just want this to
work because we are going to need this for our application)
Dim CN As New Microsoft.Data.Odbc.OdbcConnection(DSN=PostgreSQL30)
Dim CM As New Microsoft.Data.Odbc.OdbcCommand(BEGIN; SELECT
p0012_fetch_supplier('1','1','funccursor'); FETCH ALL IN
funccursor; COMMIT;, CN)
Dim DA As New Microsoft.Data.Odbc.OdbcDataAdapter(CM)
Dim DT As New DataTable()
Try
CM.CommandType = CommandType.Text
DA.SelectCommand.Connection.Open()
DA.Fill(DT)
DA.SelectCommand.Connection.Close()
Catch ex As Microsoft.Data.Odbc.OdbcException
Debug.WriteLine(ex.Message)
Debug.WriteLine(ex.Source)
Debug.WriteLine(ex.HelpLink)
Finally
CN.Close()
It does not raise an exception so there are no real 'errors'; it just does
not give 'data' to the ADO.NET container.
If I am asking this in the wrong mailinglist, then please point me in the
right direction. I don't think that I will get a answer at Microsoft.com so
that is why I ask it here ..
Thx in advance
Jonas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How do you write this query?
I have a table Table test Column |Type| Modifiers ++-- data| integer| not null data1 | character varying(128) | not null data2 | character varying(128) | not null (Note: data is NOT the primary key.) And select * from test returns data | data1 | data2 --+---+--- 1 | foo | bar 2 | greg | bar 3 | pooh | bar 4 | dah | peng I need a query that returns me the data1 that satisfies the logic of the following pseudo code: 1: select data2 into @out from test where data1 = 'pooh' 2: select data1 from test where data2 = @out and data = 3 What do I do? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(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
Re: [SQL] How do you write this query?
On Thursday 31 Oct 2002 6:21 pm, Wei Weng wrote: data | data1 | data2 --+---+--- 1 | foo | bar 2 | greg | bar 3 | pooh | bar 4 | dah | peng I need a query that returns me the data1 that satisfies the logic of the following pseudo code: 1: select data2 into out from test where data1 = 'pooh' 2: select data1 from test where data2 = out and data = 3 The most literal would be something like: SELECT t1.data1 FROM test t1 WHERE t1.data=3 AND t1.data2 IN (SELECT t2.data2 FROM test t2 WHERE t2.data1='pooh') You can probably get away without the t1/t2 stuff but that should make things clear. Since Postgresql isn't very good at optimising IN, you might want to rewrite it as an EXISTS query instead - see the manuals and mailing list archives for details. HTH -- Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How do you write this query?
On 31 Oct 2002, Wei Weng wrote: and yet another equivalent query: SELECT f1.data1 from test f1,test f2 where f1.data=3 and f1.data2 = f2.data2 and f2.data1='pooh'; I have a table Table test Column|Type| Modifiers ++-- data| integer| not null data1 | character varying(128) | not null data2 | character varying(128) | not null (Note: data is NOT the primary key.) And select * from test returns data | data1 | data2 --+---+--- 1 | foo | bar 2 | greg | bar 3 | pooh | bar 4 | dah | peng I need a query that returns me the data1 that satisfies the logic of the following pseudo code: 1: select data2 into @out from test where data1 = 'pooh' 2: select data1 from test where data2 = @out and data = 3 What do I do? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(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 == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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
