[SQL] using top-level aggregate values in subqueries

[Thomas F. O'Connell]

from the docs, i know that if you have two tables, foo and bar, you can 
write a query such as

select f.bling
from foo f
where f.id = (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
);

what i'm wondering is if you need that subquery in two places in a query 
if there's some way to cache it at the top level.

for instance, if i were shooting for

select f.id
from foo f, ola o
where f.id = (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
)
and o.id != (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
)

is there some way to grab the value returned by the subquery in the 
superquery and use the value instead of running the subquery twice?

i'm not looking for an optimized version of my example (unless it 
answers the question of the bigger picture); i'd rather know if there's 
some way to access top-level aggregates from within a subquery.

or find out that postgres is smart enough to recognize bits of SQL in a 
query that are identical and do its own internal caching.

generically stated, my question is:

is there some way, without writing a function, to calculate an aggregate 
value in a query that is used in multiple subqueries without needing to 
run an aggregating query multiple times?

i know it only amounts to syntactic sugar, but, as such, it would be 
pretty sweet.

thanks.

-tfo


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] the optimizer and exists

[Thomas F. O'Connell]

i think i might've stumbled across a tiny defect in the optimizer. 
unfortunately, i haven't the knowledge of the code to know where to 
begin looking at how to address this problem.

anyway, consider the following:

create table foo(
id int2
);

create table bar(
id int2
foo_id int2 references foo( id )
);

imagine that the tables are populated.

now, consider the query

select b.foo_id
from bar b
where b.id = 
and
exists(
select *
from foo f
where b.foo_id = f.id
and b.id = 
);

now consider the same query with "select " in place of "select 
*" in the EXISTS subquery.

explain analyze indicates that the constant version always runs a little 
bit faster. shouldn't the optimizer be able to determine that it isn't 
necessary actually to read a row in the case of EXISTS? i'm assuming 
that's where the overhead is coming into play.

i realize this is minutiae in comparison to other aspects of 
development, but it is another small performance boost that could be 
added since i imagine many people, myself included, find it more natural 
to throw in "select *" rather than "select ".

i didn't see this on the current lists or TODO, but if it's a dupe, i 
apologize for the noise. i also apologize for not being able to patch 
it, myself!

-tfo


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Extracting fieldnames from a TABLE

[Thomas F . O'Connell]

One way to do this is to use the column_info database handle method.
Here's a little perl script that accepts a table name as an argument 
and returns the column names:

#!/usr/bin/perl
use DBI;
use strict;
my( $database, $table ) = @ARGV;
my $dbh = DBI->connect( "dbi:Pg:dbname=$database", 'postgres' );
my $sth = $dbh->column_info( undef, 'public', $table, '%' );
$sth->execute;
while( my @row = $sth->fetchrow_array ) {
print join( ' ', $row[ 3 ] ), "\n";
}
$sth->finish;
$dbh->disconnect;
This could be easily modified to stick the contents of $row[ 3 ] into 
an array. You'd have to modify the user and schema as appropriate for 
your database.

The fourth parameter to column_info is a wildcard so you get everything.
-tfo
On Sep 1, 2004, at 10:14 AM, Erik Wasser wrote:
Hi community,
I would like to retrieve all the fieldnames of a given table. In the
perl module Tie::DBI[1] i found the following fragment:
   $dbh->prepare("LISTFIELDS $table");
in the case the DB supports this (Tie::DBI thinks so for Pg) or the
alternative is:
   $dbh->prepare("SELECT * FROM $table WHERE 0=1");
The first one doesn't work in my PostgreSQL 7.4.3:
% LISTFIELDS foobar;
ERROR:  syntax error at or near "LISTFIELDS" at character 1
%
and the seconds one looks ugly. Is there a solution for the problem?
Greetings
[1]http://search.cpan.org/~lds/Tie-DBI-0.93/lib/Tie/DBI.pm
--
So long... Fuzz

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] SQL confusion

[Thomas F . O'Connell]

This is untested, but it might be enough to get you started:
SELECT namecounter
FROM name n
WHERE NOT EXISTS (
SELECT 1
FROM name
WHERE hh > 0
AND famnu = n.famnu
)
GROUP BY famnu
HAVING birthdate = min( birthdate );
What I'm trying to do here is grab all families that don't have a head 
of household, group them by family, and get only the namecounter 
corresponding to the minimum birthdate for that family.

If I recall, I've had some trouble using HAVING with min/max in ways 
that seem intuitive to me, but this might help get you started.

-tfo
On Oct 9, 2004, at 3:39 PM, Andrew Ward wrote:
I'm trying to figure out how to do a particular query,
and I'm beating my head against a wall.  Here's my
situation:
I'm running postgres 7.3.2 on linux, and making my
requests from Perl scripts using DBD::Pg.  My table
structure is as follows (irrelevant cols removed)
CREATE TABLE name (
namecounter integer NOT NULL,
firstmiddle character varying(64) NOT NULL,
lastname character varying(64) NOT NULL,
birthdate date,
hh smallint,
famnu integer,
);
Each row represents a person with a unique
namecounter.  Families share a famnu, and usually one
person in a family is marked as head of household
(hh>0), with everyone else hh=0.  However, there are a
few families with nobody marked as hh, and I'd like to
elect one by age.  The query I'm trying to do is to
pull one person from each household, either the head
of household if available, or the eldest if not.  I
want them sorted by last name, so I'd prefer to find
them all in one query, no matter how ugly and nested
it has to be.
I can pull the list with hh>0 easily enough, but I'm
not sure how to pull out the others.
I realize that this could be done through some looping
in the Perl script, but I'd like to avoid pulling the
whole list into memory in case the list gets long.  My
preference is to just handle one record at a time in
Perl if possible.
Help?
Andrew Ward
[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of 
broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] help on a query

[Thomas F . O'Connell]

A query that should get the job done is:
SELECT registration_id
FROM registrations r
WHERE NOT EXISTS (
SELECT 1
FROM receipts
WHERE registration_id = r.registration_id
);
There might be a more efficient version with JOINs that don't require a 
subquery, but this should get you started.

-tfo
On Oct 7, 2004, at 10:03 PM, Michelle Murrain wrote:
Hi all,
This is one of those things I know I should know, but it's not coming 
to me. It's probably really simple.

I have two related tables, registrations and receipts, related by the 
field registration_id.

So registrations looks kinda like:
registration_id bigint (primary key)
foo varchar(10)
bar varchar(20)
and receipts looks like:
receipt_id bigint (primary key)
registration_id bigint (foreign key)
amount float
baz varchar(10)
If someone has paid, there is a row in the receipts table for that 
registration ID#.

I need to find a list of the registration IDs that *don't* have an 
entry in the receipts table.

Thanks in advance!!!
--
.Michelle
--
Michelle Murrain
mmurrain at dbdes dot com
413-222-6350 ph
617-889-0929 ph
952-674-7253 fax <--- new
Page: [EMAIL PROTECTED]
AIM:pearlbear0 ICQ:129250575
Skype: pearlbear
Jabber: [EMAIL PROTECTED]
"I see all the different religious traditions as paths for the 
development of inner peace, which is the true foundation of world 
peace. These ancient traditions come to us as a gift from our common 
past. Will we continue to cherish it as a gift and hand it over to the 
future generations as a legacy of our shared desire for peace?"  - His 
Holiness the Dalai Lama

---(end of 
broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] help on a query

[Thomas F . O'Connell]

I think the OUTER JOIN version is probably more efficient, but EXPLAIN 
would tell you.

-tfo
On Oct 8, 2004, at 8:02 AM, CHRIS HOOVER wrote:
Just curious, what is wrong with the first way of coding the solution?
--( Forwarded letter 1 follows )-
Date: Fri, 8 Oct 2004 08:44:23 +0400
To: Thomas.F.O'[EMAIL PROTECTED], [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
From: [EMAIL PROTECTED]
Sender: [EMAIL PROTECTED]
Subject: Re: [SQL] help on a query
On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote:
A query that should get the job done is:
SELECT registration_id
FROM registrations r
WHERE NOT EXISTS (
SELECT 1
FROM receipts
WHERE registration_id = r.registration_id
);
Don't, PLEASE, don't !!!
drive this way :
SELECT r.registration_id
 FROM registrations AS r
LEFT OUTER JOIN receipts AS rec
 ON rec.registration_id = r.registration_id
WHERE rec.registration_id IS NULL;

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Counting Distinct Records

[Thomas F . O'Connell]

I am wondering whether the following two forms of SELECT statements are 
logically equivalent:

SELECT COUNT( DISTINCT table.column ) ...
and
SELECT DISTINCT COUNT( * ) ...
If they are the same, then why is the latter query much slower in 
postgres when applied to the same FROM and WHERE clauses?

Furthermore, is there a better way of performing this sort of operation 
in postgres (or just in SQL in general)?

Thanks!
-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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Counting Distinct Records

[Thomas F . O'Connell]

The specific problem I'm trying to solve involves a user table with 
some history.

Something like this:
create table user_history (
user_id int
event_time_stamp timestamp
);
I'd like to be able to count the distinct user_ids in this table, even 
if it were joined to other tables.

-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 17, 2004, at 8:52 AM, Stephan Szabo wrote:
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
Hmm. I was more interested in using COUNT( * ) than DISTINCT *.
I want a count of all rows, but I want to be able to specify which
columns are distinct.
I'm now a bit confused about exactly what you're looking for in the 
end.
Can you give a short example?

That's definitely an interesting approach, but testing doesn't show it
to be appreciably faster.
If I do a DISTINCT *, postgres will attempt to guarantee that there 
are
no duplicate values across all columns rather than a subset of 
columns?
Is that right?
It guarantees one output row for each distinct set of column values 
across
all columns.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] How to update dependent tables AND design considerations

[Thomas F . O'Connell]

Have you looked at the documentation on triggers?
-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 Jan 23, 2005, at 11:23 AM, Klaus W. wrote:
Hi!
Because i got no answer in pgsql.novice, i'll try it here. But maybe it
was even too easy for .novice?
Lets say there are three tables:
CREATE TABLE c (id SERIAL, data VARCHAR(20));
CREATE TABLE b (id SERIAL, c_id INTEGER REFERENCES c, data 
VARCHAR(20));
CREATE TABLE a (id SERIAL, b_id INTEGER REFERENCES b, data 
VARCHAR(20));

Now i have to insert some data into this table structure.
In my old mysql days i would have inserted into c, look after
the id, insert it into b, look after the id, insert into a...
Of course this could be done here too, but i think it's the worst
case.
Another idea was to create a VIEW which is updatable and
insertable by RULES. But this solution simply shifts the Problem
to the rule definition.
Next idea was using a pl/pgsql function. But still the same
problem: how to do?
Idea:
INSERT INTO TABLE c VALUES (DEFAULT, 'asdfasdfasfd');
INSERT INTO TABLE b VALUES (DEFAULT, currval('c_id_seq'), 
'asdfasfasf');


Good Idea? Is this the normal way?
But what about this case:
There is the following table:
CREATE TABLE old (data_a VARCHAR(20), data_b VARCHAR(20), data_c 
VARCHAR(20);

containig Data that should be incorporated to the above mentioned
table structure. With my solution i have to read the data
with an application and split it into subsequent INSERT
statements. This could not be a good Idea.
Of course i could define the already mentioned VIEW, write
some rules for updating and inserting and INSERT the data
from old table into the VIEW. But is this the
usual way?
Isn't there something available like an INSERT to multiple
tables?
With real updates this should be easier, because the
datasets are already existing and can be joined
within FROM of the UPDATE Statement. But what about
this case:
I get a dataset: ('data a', 'data b', 'data c'). But
the corresponding subset in table a and b already exists.
Do i have to check in my application wheather the
Dataset in table c exists or not and do an seperate
INSERT myself? What about if the Data is coming from
another table and not from an application?
Should i make my UPDATE rule of a possible VIEW
doing this JOB?
Unfortunately the available tutorials cover only quite
simple cases. But my projekt may have up to five or more
dependency layers. It would be nice to have a
readable, manageable and extensible solution.
But i'm afraid my ideas so far are not.
Thank you in advance!
Klaus
---(end of 
broadcast)---
TIP 8: explain analyze is your friend

---(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

Re: [SQL] plpgsql functions and NULLs

[Thomas F . O'Connell]

This sounds like a perfect candidate for a LEFT OUTER JOIN. See:
http://www.postgresql.org/docs/7.4/static/queries-table- 
expressions.html#QUERIES-FROM

Yours would looks something like:
SELECT *
FROM ...
LEFT JOIN candidate AS c
ON <...>.omcr_id = c.omcr_id
AND ...
-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 Jan 30, 2005, at 1:41 PM, Don Drake wrote:
OK, I have a function that finds records that changed in a set of
tables and attempts to insert them into a data warehouse.
There's a large outer loop of candidate rows and I inspect them to see
if the values really changed before inserting.
My problem is that when I look to see if the row exists in the
warehouse already, based on some IDs, it fails when an ID is NULL.
The ID is nullable, so that's not a problem.
But I'm forced to write an IF statement looking for the potential NULL
and write 2 queries:
IF omcr_id is null
   select * from 
   WHERE omcr_id is NULL
   AND ...
ELSE
   select * from 
   WHERE omcr_id=candidate.omcr_id
   AND 
END IF;
IF FOUND
...
Is there a way to do the lookup in one statement?? This could get ugly
quick.  I'm using v7.4.
Thanks.
-Don
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
312-560-1574
---(end of  
broadcast)---
TIP 4: Don't 'kill -9' the postmaster

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] plpgsql functions and NULLs

[Thomas F . O'Connell]

As far as I know, you didn't post your actual table definitions (or 
full queries) earlier, so I'm not exactly sure what you mean.

-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 Jan 31, 2005, at 3:06 PM, Don Drake wrote:
My outer query to get the candidates has an outer join, that works
just fine and I get the null OMCR_ID's.
It's when I have to query the dimension table (no joins) to see if a
row exists with a (sometimes) null OMCR_ID I'm forced to write 2
queries, when I think I should only have to write one.
Thanks.
-Don

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Making NULL entries appear first when ORDER BY ASC

[Thomas F . O'Connell]

Yup. Got it. Wasn't thinking clearly about what expression meant. 
Thanks!

-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 Feb 23, 2005, at 2:33 PM, Bruno Wolff III wrote:
On Wed, Feb 23, 2005 at 13:54:50 -0600,
  "Thomas F.O'Connell" <[EMAIL PROTECTED]> wrote:
How would one know from the reference material that it is possible to
include IS NOT NULL in an ORDER BY clause?
Similarly, other than the FAQ, I've never been able to tell from the
SELECT documentation why ORDER BY random() works.
From the SELECT command documentation:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
Notice that for ORDER BY you can supply an expression. That should be 
a big
clue why you can use IS NOT NULL and random() in an ORDER BY clause.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] query

[Thomas F . O'Connell]

You should be able to use the CURRENT_DATE function in place of sysdate.
You might need to cast the 1 explicitly to an interval.
As in:
CURRENT_DATE - '1 day'::interval
-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 Mar 17, 2005, at 4:57 AM, Chandan_Kumaraiah wrote:
Hi, 
In oracle we write sysdate-1
For example,we write a query (select * from table1 where 
created_date>=sysdate-1).Whats its equivalent in postgre?

Chandan

---(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

Re: [SQL] DROP IF ...

[Thomas F. O'Connell]

The following function takes a table name as a parameter and drops  
the table and returns true if there are zero rows (otherwise, it  
returns false):


CREATE OR REPLACE FUNCTION dropzero( varchar ) RETURNS bool AS '
DECLARE
zerotable ALIAS FOR $1;
zerocurs refcursor;
rowcount int;
BEGIN
OPEN zerocurs FOR EXECUTE ''SELECT COUNT( * ) FROM '' ||  
zerotable;

FETCH zerocurs INTO rowcount;
CLOSE zerocurs;
IF rowcount = 0 THEN
EXECUTE ''DROP TABLE '' || zerotable;
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql';

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On May 24, 2005, at 12:44 PM, CG wrote:


PostgreSQL 7.4 ...

I'm trying to find a way to drop a table via SQL if it contains 0  
rows. Here

was my thought:

CREATE OR REPLACE FUNCTION dropif(text, bool)
  RETURNS bool AS
'DECLARE
  tblname ALIAS FOR $1;
  condition ALIAS FOR $2;
BEGIN
  IF (condition) THEN
EXECUTE(\'DROP TABLE "\' || tblname || \'";\');
  END IF;
  RETURN \'t\'::bool;
END;'
  LANGUAGE 'plpgsql' VOLATILE;

... then ...

BEGIN;
CREATE TABLE testtbl (i int4);
SELECT dropif('testtbl',(SELECT count(*)::int4 FROM testtbl) = 0);

ERROR:  relation 286000108 is still open
CONTEXT:  PL/pgSQL function "dropif" line 6 at execute statement

... It makes sense. The select is still open when the table is  
going to be

dropped. I need a different strategy.

Please advise!

CG


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Grouping Too Closely

[Thomas F. O'Connell]

I have a table that looks like this:CREATE TABLE my_table (    pkey serial PRIMARY KEY,    fkey int NOT NULL REFERENCES my_other_table( pkey ),    uid int NOT NULL REFERENCES user( pkey ),    seq1 int,    seq2 int);Basically, for each fkey that exists in my_table, there is a sequence represented by seq1, which covers every record corresponding to a given fkey. Then there is a subset of records covered by seq2, which increments over the course of a given fkey, but might span multiple records.E.g.,pkey | fkey | uid | seq1 | seq2---1        | 1    | 1    | 1        | 12        | 1    | 2    | 2        | 1...What I'd like to be able to do is select all records corresponding to the minimum value of seq1 for each value of seq2 corresponding to a given fkey (with a lower bound on the value of seq2).My first attempt looked like this:SELECT fkey, uid, seq2FROM my_tableWHERE seq2 > 2GROUP BY fkey, seq2, uid, seq1HAVING seq1  = min( seq1 )but this groups too closely to return the desired results.My next attempt looked like this (where I use the shorthand for min in the subquery):SELECT fkey, uid, seq2FROM my_table AS mt1WHERE mt1.seq2 > 2AND ( mt1.uid, hh1.seq1 ) IN (        SELECT mt2.player_id, mt2.order_no        FROM my_table AS mt2        WHERE mt2.fkey = mt1.fkey        AND mt2.seq2 = mt1.seq2        GROUP BY mt2.seq1, mt2.uid        ORDER BY mt2.seq1 ASC        LIMIT 1)GROUP BY mt1.holdem_game_id, mt1.holdem_round_type_id, mt1.player_idThis seems like it works, but it is abominably slow, running on the order of days across 1.5 million rows rather than the seconds (or preferably milliseconds) I'd prefer.I have this annoying feeling that I'm overlooking a reasonably efficient in-between query.-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC  Strategic Open Source: Open Your i™  http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005  

Re: [SQL] Grouping Too Closely

[Thomas F. O'Connell]

This doesn't give me quite what I'm looking for because I need there  
to be only one of each possible value of seq2 to be returned for each  
value of fkey.


--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Jun 24, 2005, at 11:22 PM, Greg Sabino Mullane wrote:


What I'd like to be able to do is select all records corresponding to
the minimum value of seq1 for each value of seq2 corresponding to a
given fkey (with a lower bound on the value of seq2).


I'm not sure how uid figures in, but would this do what you want?:

SELECT fkey, uid, seq2, min(seq1)
FROM my_table
WHERE seq2 > 2
GROUP BY fkey, uid, seq2
ORDER BY 1,2,3;


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] sql function: using set as argument

[Thomas F. O'Connell]

Is there a reason not to build it in as a sub-query?E.g., if you have a function get_count( int ): SELECT count(b_column)FROM some_tableWHERE some_field_1 in (    SELECT a_column    FROM a_table    WHERE some_condition)AND some_field_2 = $2; --Thomas F. O'ConnellCo-Founder, Information ArchitectSitening, LLCStrategic Open Source: Open Your i™http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-5150615-469-5151 (fax) On Aug 10, 2005, at 9:30 AM, Akshay Mathur wrote: Hi, I have a function that returns a set. Looks like:    Select a_column from a_table where some_condition; I want to use output of this function as an argument of another function    Second looks like:    Get_count(result_set_of_function_1, int)        Select count(b_column) from some_table where some_field_1 in ($1) and some_field_2 = $2; Please suggest how can I do that? Regards, akshay  ---Akshay MathurSMTS, Product VerificationAirTight Networks, Inc. (www.airtightnetworks.net)O: +91 20 2588 1555 ext 205F: +91 20 2588 1445    

Re: [SQL] Numerical variables in pqsql statements

[Thomas F. O'Connell]

Michael,

PL/pgSQL variable interpolation works similarly to that in other  
popular programming languages. If you have a statement -- whether  
it's PERFORM, SELECT INTO, or EXECUTE -- a variable will get  
interpolated during parsing if not escaped in a string. Per the  
documentation, dynamic values in dynamic queries require special  
handling since they might themselves contain quotes:


http://www.postgresql.org/docs/8.0/static/plpgsql- 
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


But your temp table statement below is fine if you know that there  
will never be quotes in refer_num.


And it would work similarly if you built it dynamically via string  
concatenation as a query to be executed with quote_literal escaping  
for refer_num.


Typically, you need to protect yourself against user input to a  
function. If you're computing values in the function body that you  
know to be safe or passing in safe values generated elsewhere in the  
application, you're less likely to need to quote your variables  
explicitly or to build queries dynamically.


--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 21, 2005, at 7:43 PM, Michael Schmidt wrote:


Folks,
I'm sure this is dumb, but I'm a little confused about use of  
numerical variables in pqsql.  It was my impression one had to use  
EXECUTE on a concatenated string including quote_literal() for the  
variable containing the value.  This can be quite a bit of  
trouble.  I just wrote a function that included the statement :


CREATE TEMPORARY TABLE author_names AS
  SELECT ra.ref_auth_key, a.last_name, a.first_name
  FROM ref_auth ra INNER JOIN authors a
  ON (ra.author_num = a.author_key)
  WHERE ra.ref_num = refer_num;

where refer_num is integer.  This worked (surprisingly, to me).   
So, what is the rule regarding variables?  Would this query work if  
I concatenated a string together, including quote_literal 
(refer_num) and then EXECUTEd it?


Thanks and sorry to be so stupid.



---(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] question

[Thomas F. O'Connell]

Matt,

In PostgreSQL 8.0.3, I see:

postgres=# select nullif( '1', '' );
nullif

1
(1 row)

postgres=# select nullif( '', '' ) is null;
?column?
--
t
(1 row)

What behavior are you expecting?

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 24, 2005, at 12:05 AM, Matt A. wrote:


I have a rating section on a website. It has radio
buttons with values 1-5 according to the rating level.
Lastly there is a null option for n/a. We use null for
n/a so it's excluded from the AVG() calculations.

We used nullif('$value','') on inserts in mssql.  We
moved to postgres and love it but the nullif() doesn't
match empty strings to each other to return null other
than a text type, causing an error. This is a major
part of our application.

AKA nullif('1','') would insert 1 as integer even
though wrapped in ''. Also nullif('','') would
evaluate NULL and insert the "not a value"
accordingly.

Is there a workaround for this so it doesn't break our
rating system? We cannot always enter a value for a
integer column. Is there any other way to accomplish
this? Please help.




__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster




---(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] Numerical variables in pqsql statements

[Thomas F. O'Connell]

Well, browse through this list:

http://www.postgresql.org/docs/books/

I can't make any recommendations, as I am fairly familiar with the  
online documentation, which, when supported by the community, seems  
to be pretty good.


--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 29, 2005, at 4:05 PM, Michael Schmidt wrote:


Mr. O'Connell,
Thanks so much for the insights.  Sorry about the basic nature of  
the question - perhaps a "PostgreSQL for Dummies" book would help me!

Michael Schmidt




---(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] Searching for results with an unknown amount of data

[Thomas F. O'Connell]

On Sep 2, 2005, at 2:40 PM, DownLoad X wrote:


Hi,

I've got two tables, A and B, the first one containing columns

A_ID | info

where A_ID is primary, so that this table stores various  
information about an object, and the second containing columns


A_ID | property

where property is an integer referring to a particular property  
that an object may possess.  I've seperated these into two tables  
so that an object may have several (ie an unknown number of)  
properties.


Now, I want to find all objects that have at most properties 1,2,3,  
say (so something with (1,2) is okay, as is (1,2,3)). I can't see a  
way to do this -- can anyone help?
Also, what if I want to find all the objects possessing properties  
4,5,6, as well as possibly other things? I've done it with nested  
SELECTs (constructed programmatically), but I think this is quite  
slow and not a very pleasing solution.


Obviously, both these things will need to be done for an arbitrary  
list of integers.


Thanks,
DL


Without knowing more about your data or schema, couldn't you do  
something like


SELECT A_ID, property
FROM "B"
WHERE property IN ( 1, 2, 3 );

To accommodate arbitrary lists, I can't imagine how you'd do it  
without using a PL, but you could still build your IN clause  
programmatically.


--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] EXECUTE with SELECT INTO variable, or alternatives

[Thomas F. O'Connell]

Per the docs:

"The results from SELECT commands are discarded by EXECUTE, and  
SELECT INTO is not currently supported within EXECUTE. So there is no  
way to extract a result from a dynamically-created SELECT using the  
plain EXECUTE command. There are two other ways to do it, however:  
one is to use the FOR-IN-EXECUTE loop form described in Section  
35.7.4, and the other is to use a cursor with OPEN-FOR-EXECUTE, as  
described in Section 35.8.2."


http://www.postgresql.org/docs/8.0/static/plpgsql- 
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


So you've already hit upon one of your options.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Sep 29, 2005, at 1:16 PM, Mario Splivalo wrote:


I can assign a value to a variable in several ways:

myVar := (SELECT col FROM table WHERE somecondition...)
myVar := col FROM table WHERE somecondtition...
SELECT col INTO myVar FROM table WHERE somecondition

How do I do any of the above using EXECUTE? I need to be able to  
assign

the value to a variable, a value returned by a querry on a temporary
table.

So far I have found workaround like this:

myRec record;

FOR rec IN EXECUTE ''SELECT col FROM table WHERE somecondition''
LOOP
myVar := rec
END LOOP

Obviously, the above SELECT query returns only one row. Still, if is
realy inconvinient to have FOR...LOOP...END LOOP construct for  
assigning

the value to a variable 'read' from the temporary table.

Mario


---(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] Strange join...maybe some improvements???

[Thomas F. O'Connell]

What indexes do you have on these tables?And have you ANALYZEd all three recently? --Thomas F. O'ConnellCo-Founder, Information ArchitectSitening, LLCOpen Source Solutions. Optimized Web Development.http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-5150615-469-5151 (fax) On Oct 12, 2005, at 8:52 AM, Andy wrote:  I have 3 tables: CREATE TABLE orders(  id int4 SERIAL,  id_ag int4,  id_modell int4 ->> this is linked to the modell.id )    CREATE TABLE modell(  id int4 SERIAL,  id_hersteller int4)    CREATE TABLE contactpartner(  id int4 SERIAL,  id_ag int4, ->> this is linked to order.id_ag or modell.id_hersteller  id_user int4  ).   I get a list of id_ag from the contactpartner which belongs to a user(AG_LIST). Then I have to selectselect/count all the data's from the order table that have the order.id_ag in the AG LIST or which have the modell.id_hersteller in the AG_LIST.    I have this query:   SELECT count(o.id) FROM orders oINNER JOIN modell m ON m.id=o.id_modellWHERE o.id_ag IN (SELECT id_ag FROM contactpartner cp WHERE id_user=15) OR m.id_hersteller IN (SELECT id_ag FROM contactpartner cp WHERE id_user=15)   Aggregate  (cost=7828.60..7828.60 rows=1 width=4) (actual time=1145.150..1145.151 rows=1 loops=1)  ->  Hash Join  (cost=1689.64..7706.32 rows=48913 width=4) (actual time=153.059..1136.457 rows=9395 loops=1)    Hash Cond: ("outer".id_modell = "inner".id)    Join Filter: ((hashed subplan) OR (hashed subplan))    ->  Seq Scan on orders o  (cost=0.00..3129.17 rows=65217 width=12) (actual time=0.031..94.444 rows=65217 loops=1)    ->  Hash  (cost=1218.07..1218.07 rows=66607 width=8) (actual time=151.211..151.211 rows=0 loops=1)  ->  Seq Scan on modell m  (cost=0.00..1218.07 rows=66607 width=8) (actual time=0.044..87.154 rows=66607 loops=1)    SubPlan  ->  Index Scan using contactpartner_id_user_idx on contactpartner cp  (cost=0.00..6.02 rows=2 width=4) (actual time=0.010..0.018 rows=4 loops=1)    Index Cond: (id_user = 15)  ->  Index Scan using contactpartner_id_user_idx on contactpartner cp  (cost=0.00..6.02 rows=2 width=4) (actual time=0.092..0.116 rows=4 loops=1)    Index Cond: (id_user = 15)Total runtime: 1145.689 ms   I tried also this one:   SELECT count(o.id) FROM orders oINNER JOIN modell m ON m.id=o.id_modellINNER JOIN contactpartner cp ON cp.id_user=15 AND (o.id_ag=cp.id_ag OR cp.id_ag=m.id_hersteller)   Aggregate  (cost=11658.63..11658.63 rows=1 width=4) (actual time=1691.570..1691.570 rows=1 loops=1)  ->  Nested Loop  (cost=7752.40..11657.27 rows=542 width=4) (actual time=213.945..1683.515 rows=9416 loops=1)    Join Filter: (("inner".id_ag = "outer".id_ag) OR ("outer".id_ag = "inner".id_hersteller))    ->  Index Scan using contactpartner_id_user_idx on contactpartner cp  (cost=0.00..6.02 rows=2 width=4) (actual time=0.108..0.188 rows=4 loops=1)  Index Cond: (id_user = 15)    ->  Materialize  (cost=7752.40..8723.57 rows=65217 width=12) (actual time=37.586..352.620 rows=65217 loops=4)  ->  Hash Join  (cost=1677.59..7368.18 rows=65217 width=12) (actual time=150.220..1153.872 rows=65217 loops=1)    Hash Cond: ("outer".id_modell = "inner".id)    ->  Seq Scan on orders o  (cost=0.00..3129.17 rows=65217 width=12) (actual time=0.034..95.133 rows=65217 loops=1)    ->  Hash  (cost=1218.07..1218.07 rows=66607 width=8) (actual time=149.961..149.961 rows=0 loops=1)  ->  Seq Scan on modell m  (cost=0.00..1218.07 rows=66607 width=8) (actual time=0.032..86.378 rows=66607 loops=1)Total runtime: 1696.253 ms    but this brings me some double information(the same o.id) in the situation in which the o.id_ag and m.id_hersteller are different, but still both in the AG_LIST.      Is there any way to speed up this query???   Regards,  Andy.

Re: [SQL] SQL Functions

[Thomas F. O'Connell]

On Oct 21, 2005, at 9:19 AM, [EMAIL PROTECTED] wrote:

I have been trying to find a way to return more than one but  
different types of

variables. How do I return more than one but mix types of variables.
Any help is appriaciated.
Thanks;


In PostgreSQL 8.1, you'll have output parameters available.

http://developer.postgresql.org/docs/postgres/plpgsql.html#PLPGSQL- 
OVERVIEW


--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Open Source Solutions. Optimized Web Development.

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings