date:20080819

Re: [SQL] Cursors..

2008-08-19 Thread Yura Gal

Take a look at 
http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html
and you'll find answer you need.

> I need to convert this cursor of Oracle to Postgres...
> I wait for your help!!

-- 
Best regards, Yuri.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Join question

2008-08-19 Thread Edward W. Rouse

I thought of that, but it does violate table constraints.

 

Edward W. Rouse

 

From: Oliveiros Cristina [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 18, 2008 2:00 PM
To: pgsql-sql@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [SQL] Join question

 

I don't understand your count(total) expression... 

It doesnt work, because apparently you dont have any "total" column...

Apparently, you meant count(color)

 

The problem is that you are grouping by a.org,a.user and on  table "a" u 
actually dont have any "null" users...

 

Well, if it is to include "null" users, a quick and dirty solution I can think 
of would be to add a "dummy" null user to every diferent org on table a and then

substitute your LEFT OUTER JOIN condition by this one :

 

from a left  join b

 

on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null )))

 

 

Now, I don' know if "null" users on table "a" will violate any constraints you 
may have (e.g. NOT NULL) ...

 

I know This is not a very elegant solution, but seems to give the results you 
need

 

Best,

Oliveiros

- Original Message - 

From: Daniel Hernandez   

To: pgsql-sql@postgresql.org ; [EMAIL PROTECTED] 

Sent: Monday, August 18, 2008 5:30 PM

Subject: Re: [SQL] Join question

 

have you tried a right Join?


Daniel Hernndez.
San Diego, CA.
"The more you learn, the more you earn".
Fax: (808) 442-0427


-Original Message-
From: "Edward W. Rouse" [EMAIL PROTECTED]
Date: 08/15/2008 09:48 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join question




I have 2 tables, both have a user column. I am currently using a left join from 
table a to table b because I need to show all users from table a even those not 
having an entry in table b. The problem is I also have to include items from 
table b with that have a null user. There are some other criteria as well that 
are simple where clause filters. So as an example:

Table a:

Org|user

A| emp1

B| emp1

B| emp2

B| emp3

C| emp2

Table b:

Org|user|color

A   |emp1|red

A   |emp1|blue

A   |null|pink

A   |null|orange

B   |emp1|red

B   |emp3|red

B   |null|silver

C   |emp2|avacado

If I:

select org, user, count(total)

from a left join b

on (a.org = b.org and a.user = b.user)

where a.org = ‘A’

group by a.org, a.user

order by a.org, a.user

I get:

Org|user|count

A|emp1|2

A|emp2|0

A|emp3|0

But what I need is:

A|emp1|2

A|emp2|0

A|emp3|0

A|null|2

Thanks,

Edward W. Rouse

Re: [SQL] Join question

2008-08-19 Thread Edward W. Rouse

I have tried left, right outer and inner. 

 

Edward W. Rouse

 

From: Daniel Hernandez [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 18, 2008 12:30 PM
To: pgsql-sql@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [SQL] Join question

 

have you tried a right Join?


Daniel Hernndez.
San Diego, CA.
"The more you learn, the more you earn".
Fax: (808) 442-0427


-Original Message-
From: "Edward W. Rouse" [EMAIL PROTECTED]
Date: 08/15/2008 09:48 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join question

I have 2 tables, both have a user column. I am currently using a left join from 
table a to table b because I need to show all users from table a even those not 
having an entry in table b. The problem is I also have to include items from 
table b with that have a null user. There are some other criteria as well that 
are simple where clause filters. So as an example:

 

Table a:

Org|user

A| emp1

B| emp1

B| emp2

B| emp3

C| emp2

 

Table b:

Org|user|color

A   |emp1|red

A   |emp1|blue

A   |null|pink

A   |null|orange

B   |emp1|red

B   |emp3|red

B   |null|silver

C   |emp2|avacado

 

If I:

 

select org, user, count(total)

from a left join b

on (a.org = b.org and a.user = b.user)

where a.org = ‘A’

group by a.org, a.user

order by a.org, a.user

 

I get:

 

Org|user|count

A|emp1|2

A|emp2|0

A|emp3|0

 

But what I need is:

 

A|emp1|2

A|emp2|0

A|emp3|0

A|null|2

 

Thanks,

Edward W. Rouse

Re: [SQL] Join question

2008-08-19 Thread Oliveiros Cristina

Already tried making two queries and then outputting the UNION of the results?

The second one could be something like this...

SELECT org,null,COUNT(color)
FROM b
WHERE user IS NULL
AND org = 'a'
GROUP BY org

Best,
Oliveiros
  - Original Message - 
  From: Edward W. Rouse 
  To: pgsql-sql@postgresql.org 
  Sent: Tuesday, August 19, 2008 2:36 PM
  Subject: Re: [SQL] Join question


  I thought of that, but it does violate table constraints.

   

  Edward W. Rouse

   

  From: Oliveiros Cristina [mailto:[EMAIL PROTECTED] 
  Sent: Monday, August 18, 2008 2:00 PM
  To: pgsql-sql@postgresql.org; [EMAIL PROTECTED]
  Subject: Re: [SQL] Join question

   

  I don't understand your count(total) expression... 

  It doesnt work, because apparently you dont have any "total" column...

  Apparently, you meant count(color)

   

  The problem is that you are grouping by a.org,a.user and on  table "a" u 
actually dont have any "null" users...

   

  Well, if it is to include "null" users, a quick and dirty solution I can 
think of would be to add a "dummy" null user to every diferent org on table a 
and then

  substitute your LEFT OUTER JOIN condition by this one :

   

  from a left  join b

   

  on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null 
)))

   

   

  Now, I don' know if "null" users on table "a" will violate any constraints 
you may have (e.g. NOT NULL) ...

   

  I know This is not a very elegant solution, but seems to give the results you 
need

   

  Best,

  Oliveiros

- Original Message - 

From: Daniel Hernandez 

To: pgsql-sql@postgresql.org ; [EMAIL PROTECTED] 

Sent: Monday, August 18, 2008 5:30 PM

Subject: Re: [SQL] Join question

 

have you tried a right Join?


Daniel Hernndez.
San Diego, CA.
"The more you learn, the more you earn".
Fax: (808) 442-0427


-Original Message-
From: "Edward W. Rouse" [EMAIL PROTECTED]
Date: 08/15/2008 09:48 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join question




I have 2 tables, both have a user column. I am currently using a left join 
from table a to table b because I need to show all users from table a even 
those not having an entry in table b. The problem is I also have to include 
items from table b with that have a null user. There are some other criteria as 
well that are simple where clause filters. So as an example:

Table a:

Org|user

A| emp1

B| emp1

B| emp2

B| emp3

C| emp2

Table b:

Org|user|color

A   |emp1|red

A   |emp1|blue

A   |null|pink

A   |null|orange

B   |emp1|red

B   |emp3|red

B   |null|silver

C   |emp2|avacado

If I:

select org, user, count(total)

from a left join b

on (a.org = b.org and a.user = b.user)

where a.org = ‘A’

group by a.org, a.user

order by a.org, a.user

I get:

Org|user|count

A|emp1|2

A|emp2|0

A|emp3|0

But what I need is:

A|emp1|2

A|emp2|0

A|emp3|0

A|null|2

Thanks,

Edward W. Rouse

Re: [SQL] Join question

2008-08-19 Thread Lennin Caro




--- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote:

> From: Edward W. Rouse <[EMAIL PROTECTED]>
> Subject: Re: [SQL] Join question
> To: "'Daniel Hernandez'" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org
> Date: Tuesday, August 19, 2008, 1:35 PM
> I have tried left, right outer and inner. 
> 
>  
> 
> Edward W. Rouse
> 
>  
> 
> From: Daniel Hernandez [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 18, 2008 12:30 PM
> To: pgsql-sql@postgresql.org; [EMAIL PROTECTED]
> Subject: Re: [SQL] Join question
> 
>  
> 
> have you tried a right Join?
> 
> 
> Daniel Hernndez.
> San Diego, CA.
> "The more you learn, the more you earn".
> Fax: (808) 442-0427
> 
> 
> -Original Message-
> From: "Edward W. Rouse" [EMAIL PROTECTED]
> Date: 08/15/2008 09:48 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Join question
> 
> I have 2 tables, both have a user column. I am currently
> using a left join from table a to table b because I need to
> show all users from table a even those not having an entry
> in table b. The problem is I also have to include items from
> table b with that have a null user. There are some other
> criteria as well that are simple where clause filters. So as
> an example:
> 
>  
> 
> Table a:
> 
> Org|user
> 
> A| emp1
> 
> B| emp1
> 
> B| emp2
> 
> B| emp3
> 
> C| emp2
> 
>  
> 
> Table b:
> 
> Org|user|color
> 
> A   |emp1|red
> 
> A   |emp1|blue
> 
> A   |null|pink
> 
> A   |null|orange
> 
> B   |emp1|red
> 
> B   |emp3|red
> 
> B   |null|silver
> 
> C   |emp2|avacado
> 
>  
> 
> If I:
> 
>  
> 
> select org, user, count(total)
> 
> from a left join b
> 
> on (a.org = b.org and a.user = b.user)
> 
> where a.org = ‘A’
> 
> group by a.org, a.user
> 
> order by a.org, a.user
> 
>  
> 
> I get:
> 
>  
> 
> Org|user|count
> 
> A|emp1|2
> 
> A|emp2|0
> 
> A|emp3|0
> 
>  
> 
> But what I need is:
> 
>  
> 
> A|emp1|2
> 
> A|emp2|0
> 
> A|emp3|0
> 
> A|null|2
> 
>  
> 
> Thanks,
> 
> Edward W. Rouse

also like this...


select id1,dato1, count(id2) from
(
select pr1.id as id1,pr1.dato as dato1,pr2.oid as id2,pr2.dato from pr1 right 
outer join pr2 on (pr1.id = pr2.oid)
) a group by id1,dato1





-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Join question

2008-08-19 Thread Edward W. Rouse

I was trying to do something like this, but couldn't get it to work. I am 
trying to follow the example you provided, but don't understand how id and oid 
relate to the example tables and which table is pr1 and pr2. Also my data has 
to match 2 constraints, not 1 (though I'm guessing that I could just add the 
other without changing anything else). And you have pr2.dato in the inner 
select but not the outer one. Is there a reason for that.

As of now I am thinking I will have to break this up into more than one 
statement.

Edward W. Rouse


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lennin Caro
Sent: Tuesday, August 19, 2008 11:59 AM
To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse
Subject: Re: [SQL] Join question




--- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote:

> From: Edward W. Rouse <[EMAIL PROTECTED]>
> Subject: Re: [SQL] Join question
> To: "'Daniel Hernandez'" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org
> Date: Tuesday, August 19, 2008, 1:35 PM
> I have tried left, right outer and inner. 
> 
>  
> 
> Edward W. Rouse
> 
>  
> 
> From: Daniel Hernandez [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 18, 2008 12:30 PM
> To: pgsql-sql@postgresql.org; [EMAIL PROTECTED]
> Subject: Re: [SQL] Join question
> 
>  
> 
> have you tried a right Join?
> 
> 
> Daniel Hernndez.
> San Diego, CA.
> "The more you learn, the more you earn".
> Fax: (808) 442-0427
> 
> 
> -Original Message-
> From: "Edward W. Rouse" [EMAIL PROTECTED]
> Date: 08/15/2008 09:48 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Join question
> 
> I have 2 tables, both have a user column. I am currently
> using a left join from table a to table b because I need to
> show all users from table a even those not having an entry
> in table b. The problem is I also have to include items from
> table b with that have a null user. There are some other
> criteria as well that are simple where clause filters. So as
> an example:
> 
>  
> 
> Table a:
> 
> Org|user
> 
> A| emp1
> 
> B| emp1
> 
> B| emp2
> 
> B| emp3
> 
> C| emp2
> 
>  
> 
> Table b:
> 
> Org|user|color
> 
> A   |emp1|red
> 
> A   |emp1|blue
> 
> A   |null|pink
> 
> A   |null|orange
> 
> B   |emp1|red
> 
> B   |emp3|red
> 
> B   |null|silver
> 
> C   |emp2|avacado
> 
>  
> 
> If I:
> 
>  
> 
> select org, user, count(total)
> 
> from a left join b
> 
> on (a.org = b.org and a.user = b.user)
> 
> where a.org = ‘A’
> 
> group by a.org, a.user
> 
> order by a.org, a.user
> 
>  
> 
> I get:
> 
>  
> 
> Org|user|count
> 
> A|emp1|2
> 
> A|emp2|0
> 
> A|emp3|0
> 
>  
> 
> But what I need is:
> 
>  
> 
> A|emp1|2
> 
> A|emp2|0
> 
> A|emp3|0
> 
> A|null|2
> 
>  
> 
> Thanks,
> 
> Edward W. Rouse

also like this...


select id1,dato1, count(id2) from
(
select pr1.id as id1,pr1.dato as dato1,pr2.oid as id2,pr2.dato from pr1 right 
outer join pr2 on (pr1.id = pr2.oid)
) a group by id1,dato1





-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Cursor

2008-08-19 Thread Fernando Hevia


> De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
] En nombre de Xavier Bermeo
> Enviado el: Sábado, 16 de Agosto de 2008 14:54
> Para: pgsql-sql@postgresql.org
> Asunto: [SQL] Cursor
>   
> Hi, guys...
> I have  problems with cursosrs.
> Anyone have an example complete the how  load and read each position of a
cursor?
> I wait your answer
> Thanks...guys 


Hi Xavier. This is a very simple example of a function with an implicit
cursor.
Regards,
Fernando.


CREATE OR REPLACE FUNCTION f_cursor()
  RETURNS void AS
$BODY$
DECLARE
  idx INTEGER;
a mytable.col1%TYPE;
b mytable.col2%TYPE;
  c mytable.col3%TYPE;
  d mytable.col4%TYPE;

BEGIN
idx :=0;

FOR a, b, c, d IN
SELECT col1, col2, col3, col4
  FROM mytable
 ORDER BY col1 ;

LOOP
   -- Comment: Every iteration in loop will read a row from the cursor 
   idx := idx + 1;
   raise notice 'Row %: [%, %, %, %]', idx, a, b, c, d;
END LOOP;
-- Comment: all rows have been read
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] LIMIT question

2008-08-19 Thread EXT-Rothermel, Peter M

I need to use a LIMIT count in a query but I also need to know how many
rows the query itself would yield without the limit.
I can do this inside a transaction like this

BEGIN
SELECT COUNT(*) from table1 where blah;
select * from table1 where blah LIMIT 1000;
COMMIT

Now I can give some feedback like your search matches 200,000 but was
limited to 1000 items.
Is there a better way to accomplish this goal?



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] LIMIT question

2008-08-19 Thread Richard Broersma

On Tue, Aug 19, 2008 at 1:58 PM, EXT-Rothermel, Peter M
<[EMAIL PROTECTED]> wrote:
> Now I can give some feedback like your search matches 200,000 but was
> limited to 1000 items.
> Is there a better way to accomplish this goal?

Here is a nice discussion on the alternatives open to you:
http://www.commandprompt.com/blogs/joshua_drake/2007/08/how_many_rows_do_i_have_anyway/


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Join question

2008-08-19 Thread Edward W. Rouse

Finally got it to work. I used 2 separate selects and a union. So one of the 
selects was like my original left outer joined select and then I unioned it 
with one that got the missed nulls from the other table.

Thanks for all the advice.

Edward W. Rouse


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse
Sent: Tuesday, August 19, 2008 2:04 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join question

I was trying to do something like this, but couldn't get it to work. I am 
trying to follow the example you provided, but don't understand how id and oid 
relate to the example tables and which table is pr1 and pr2. Also my data has 
to match 2 constraints, not 1 (though I'm guessing that I could just add the 
other without changing anything else). And you have pr2.dato in the inner 
select but not the outer one. Is there a reason for that.

As of now I am thinking I will have to break this up into more than one 
statement.

Edward W. Rouse


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lennin Caro
Sent: Tuesday, August 19, 2008 11:59 AM
To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse
Subject: Re: [SQL] Join question




--- On Tue, 8/19/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote:

> From: Edward W. Rouse <[EMAIL PROTECTED]>
> Subject: Re: [SQL] Join question
> To: "'Daniel Hernandez'" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org
> Date: Tuesday, August 19, 2008, 1:35 PM
> I have tried left, right outer and inner. 
> 
>  
> 
> Edward W. Rouse
> 
>  
> 
> From: Daniel Hernandez [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 18, 2008 12:30 PM
> To: pgsql-sql@postgresql.org; [EMAIL PROTECTED]
> Subject: Re: [SQL] Join question
> 
>  
> 
> have you tried a right Join?
> 
> 
> Daniel Hernndez.
> San Diego, CA.
> "The more you learn, the more you earn".
> Fax: (808) 442-0427
> 
> 
> -Original Message-
> From: "Edward W. Rouse" [EMAIL PROTECTED]
> Date: 08/15/2008 09:48 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Join question
> 
> I have 2 tables, both have a user column. I am currently
> using a left join from table a to table b because I need to
> show all users from table a even those not having an entry
> in table b. The problem is I also have to include items from
> table b with that have a null user. There are some other
> criteria as well that are simple where clause filters. So as
> an example:
> 
>  
> 
> Table a:
> 
> Org|user
> 
> A| emp1
> 
> B| emp1
> 
> B| emp2
> 
> B| emp3
> 
> C| emp2
> 
>  
> 
> Table b:
> 
> Org|user|color
> 
> A   |emp1|red
> 
> A   |emp1|blue
> 
> A   |null|pink
> 
> A   |null|orange
> 
> B   |emp1|red
> 
> B   |emp3|red
> 
> B   |null|silver
> 
> C   |emp2|avacado
> 
>  
> 
> If I:
> 
>  
> 
> select org, user, count(total)
> 
> from a left join b
> 
> on (a.org = b.org and a.user = b.user)
> 
> where a.org = ‘A’
> 
> group by a.org, a.user
> 
> order by a.org, a.user
> 
>  
> 
> I get:
> 
>  
> 
> Org|user|count
> 
> A|emp1|2
> 
> A|emp2|0
> 
> A|emp3|0
> 
>  
> 
> But what I need is:
> 
>  
> 
> A|emp1|2
> 
> A|emp2|0
> 
> A|emp3|0
> 
> A|null|2
> 
>  
> 
> Thanks,
> 
> Edward W. Rouse

also like this...


select id1,dato1, count(id2) from
(
select pr1.id as id1,pr1.dato as dato1,pr2.oid as id2,pr2.dato from pr1 right 
outer join pr2 on (pr1.id = pr2.oid)
) a group by id1,dato1





-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] LIMIT question

2008-08-19 Thread Tom Lane

"Richard Broersma" <[EMAIL PROTECTED]> writes:
> <[EMAIL PROTECTED]> wrote:
>> Is there a better way to accomplish this goal?

> Here is a nice discussion on the alternatives open to you:
> http://www.commandprompt.com/blogs/joshua_drake/2007/08/how_many_rows_do_i_have_anyway/

That doesn't really address the question of how to estimate the number
of rows in a *query* (as opposed to a table).

The usual advice is to do an EXPLAIN and extract the first line's
rowcount estimate.  Of course you have to realize that this is often
far from reality --- but in the context the OP gave, maybe a ballpark
estimate is good enough.

If you really need an exact count, and are willing to pay for it,
the standard way is

begin;
declare c cursor for <> ;
move forward all in c;  -- note the returned rowcount
move backward all in c; -- this, at least, is cheap
fetch 1000 from c;
commit;

The only thing this saves over just doing the full query is that you
don't have to transmit all the data to the client.  Still, that can be
an important savings.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Cursors..

Re: [SQL] Join question

Re: [SQL] Join question

Re: [SQL] Join question

Re: [SQL] Join question

Re: [SQL] Join question

Re: [SQL] Cursor

[SQL] LIMIT question

Re: [SQL] LIMIT question

Re: [SQL] Join question

Re: [SQL] LIMIT question

11 matches

Site Navigation

Mail list logo

Footer information