Need Postgresql Help

Re: Need Postgresql Help

On Monday 02 December 2002 09:44, Atul wrote:
> Respected sir,
>              Atul Pedgaonkar here from India. I am using postgresql7.2 as
> backend and for front-end, perl5.6.1. I need some help regarding
> postgresql-stored procedure. The problem is like this
>
>         1)How to create stored procedure in postgresql?
>         2)How can i use it from perl script with parameters. ?
>         3)How to return resultset from that Stored Procedure ?

You need 7.3 to do 3).
Infor on stored procedures:
$INSTALLDIR/doc/html/plpgsql.html

--

-- 
Andreas Joseph Krogh <andreak <at> officenet.no>
: What does this "kernel" program do?
- It does automated remote popcorn popping using XML-RPC to communicate
  bidirectionally with TCP/IP enabled microwave ovens.
  (Slashdot reply)

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

http://archives.postgresql.org

Re: Need Postgresql Help

On Monday 02 Dec 2002 8:42 am, Andreas Joseph Krogh wrote:
> On Monday 02 December 2002 09:44, Atul wrote:
> > Respected sir,
> >              Atul Pedgaonkar here from India. I am using postgresql7.2 as
> > backend and for front-end, perl5.6.1. I need some help regarding
> > postgresql-stored procedure. The problem is like this
> >
> >         1)How to create stored procedure in postgresql?
> >         2)How can i use it from perl script with parameters. ?
> >         3)How to return resultset from that Stored Procedure ?
>
> You need 7.3 to do 3).
> Infor on stored procedures:
> $INSTALLDIR/doc/html/plpgsql.html

1. See the manual chapter on "procedural languages" - plpgsql, pltcl, plperl
   Also see http://techdocs.postgresql.org
2. Exactly as with any other query
3. Either upgrade to 7.3 (as above) or return a cursor from the function.

--

-- 
  Richard Huxton

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo <at> postgresql.org)

Need Postgresql Help

Respected sir,
              Atul Pedgaonkar here from india. I am using postgresql7.2 as 
backend and for front-end ,perl5.6.1. I need some help regarding postgresql 
stored procedure. The problem is like this

         1)How to create stored procedure in postgresql ?
         2)How can i use it from perl script with paratmeters. ?
         3)How to return resultset from that Stored Procedure ?

I hope your kind co-operation.
Thank You,

Regards,

Atul…

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail

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

http://archives.postgresql.org

SQL99 <search or cycle clause>

Hi there!

Can anybody explain behavior of <search or cycle clause>?
Or give a link where explanation can be found?
Or something like that ?:)

To my regret, i can't figure it out
right from SQL standard description with my "brute force".

regards,

---
.evgen

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

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

CURRENT_TIMSTAMP

I created a column, dada type timstamp with time zone
and with default CURRENT_TIMSTAMP
it shows me the default is

default ('now'::text)::timstamp(6) with time zone

Then when I insert a row, the default timestamp value is

yyyy-mm-dd HH:MM:ss.mmmmm+00

where mmmmm is milliseconds.
How do I make default only yyyy-mm-dd HH:MM:ss+00 ?
Thank Q!

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

Re: [SQL] CURRENT_TIMSTAMP

On Mon, Dec 02, 2002 at 11:41:33AM -0500, Raymond Chui wrote:
> I created a column, dada type timstamp with time zone
> and with default CURRENT_TIMSTAMP
> it shows me the default is
> 
> default ('now'::text)::timstamp(6) with time zone
> 
> Then when I insert a row, the default timestamp value is
> 
> yyyy-mm-dd HH:MM:ss.mmmmm+00
> 
> where mmmmm is milliseconds.
> How do I make default only yyyy-mm-dd HH:MM:ss+00 ?
> Thank Q!

The problem isn't CURRENT_TIMESTAMP, it's your table definition.
If you create the field as timestamp(0) [in 7.3, that's timestamp(0)
with time zone, since the default has swung to no-TZ], it will keep
track of just HMS. Or put in other values for 0 for more granularity on
seconds.

Of course, you can always store the specific time and select it out
w/less resolution (using the time/date functions). In some cases, this
might be a better solution.

- J.

--

-- 

Joel BURTON  |  joel <at> joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

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

http://archives.postgresql.org

Re: Min and Max

On 29 Nov 2002, Sergio Oshiro wrote:

> How can I get the rows of the children name and its "father" such that
> they have the min child_ages?
> 
> -- the following does not return the child_name...
> select id_father, min(child_age) from children group by id_father;
> select id_father, max(child_age) from children group by id_father;

You could join one of the above with the table itself and get the result.  
Something like

select *
  from (  select id_father, min(child_age)
            from children
        group by id_father) as r,
        children
 where children.id_father = r.id_father
   and children.min = r.min;

--

-- 
/Dennis

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

Re: Min and Max

On Fri, Nov 29, 2002 at 10:55:54AM -0800, Sergio Oshiro wrote:
> Hello, everybody!
> 
> I've trouble to make a "simple"(?) query...
> 
> The following table is an example:
> 
> table: children
> id_father | child_name | child_age
> ----------+------------+------------
>         1 | John       | 2
>         1 | Joe        | 3
>         1 | Mary       | 4
>         1 | Cristine   | 4
>         2 | Paul       | 1
>         2 | Stephany   | 2
>         2 | Raul       | 5
> 
> How can I get the rows of the children name and its "father" such that
> they have the min child_ages? I expect the following rows as result:
> 
> id_father | child_name | child_age
> ----------+------------+------------
>         1 | John       | 2
>         2 | Paul       | 1
> 
> The same for the max child_ages...
> 
> id_father | child_name | child_age
> ----------+------------+------------
>         1 | Mary       | 4
>         1 | Cristine   | 4
>         2 | Raul       | 5

select distinct on (id_father) * from children order by id_father,
child_age;

will give your results

select distinct on (id_father) * from children order by id_father,
child_age desc;

will give the oldest children, but it doesn't list both mary and
christine -- it arbitrarily lists mary (you could add child_name to sort
order so it wouldn't be abitrary, but it still won't list both).

this is a weird use of distinct on, though, and perhaps cheating. a
canonical, if slower solution (and one that fixes the tie for oldest
child) is:

select id_father, 
       child_name, 
       child_age 
from   children c1 
where  not exists (select * 
                   from   children c2 
		   where  c1.id_father=c2.id_father 
		     and  c2.child_age > c1.child_age);

swap the '>' to '<' for youngest.

- J.
--

-- 

Joel BURTON  |  joel <at> joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly

Combining queries while preserving order in SQL - Help!

Hi there,

I need to do the following in one SQL query:

select field1, field2, field3, field4, field5 from table where field6 < 5
order by field1

And a totals line which shows the sum for each column.

The important part is that I need to preserve the order by of the first query.

Is there any way to do this in one query?

Thank you,

--

-- 
Casey Allen Shobe, Open Source Software Solutions
cshobe <at> osss.net / http://www.osss.net / 770-653-4526

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

http://archives.postgresql.org