Re: [SQL] createlang problme

2003-09-13 Thread Tom Lane 
"vijaykumar M" <[EMAIL PROTECTED]> writes:
> i'm using RedhatLinux7.2, on top of this i upgrade the Postgres with V7.3.3.

> After that, i tried to create a language with a createlang command.

> ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed:
> /usr/local/pgsql/lib/plpgsql.so: un
> defined symbol: xlateSqlType
> createlang: language installation failed

Looks to me like /usr/local/pgsql/lib/plpgsql.so is for some older
version of Postgres.  Where did you put the one that goes with 7.3.3?

BTW, you really ought to be using 7.3.4 not 7.3.3.  7.3.3 has a nasty
bug that can cause it to fail to restart after a crash.

regards, tom lane

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

[SQL] Q: insert/update in same statement

2003-09-13 Thread G. Ralph Kuntz, MD 
Is it possible to make a single SQL statement that will insert a row
if it does not already exist, otherwise it will just update the
fields?

Actually, just a statement that will not give an error about duplicate
keys would be enough for my application.

Something like

INSERT INTO SOME_TABLE (FIELD1, ...) VALUES (VALUE1, ...) WHERE
KEY_FIELD DOES NOT EXIST;

I am working in PostgreSQL 7.3.x.

Thanks, Ralph

---(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] Q: select query

2003-09-13 Thread G. Ralph Kuntz, MD 
I am trying to write a query to select some rows.

My data consists of rows like this

   1   a
   1   b
   1   c
   2   d
   3   e
   3   f
   ...

I would like to select the second and subsequent rows where the first
column is the same:

   1   b
   1   c
   3   f

in other words, all but the first row of a group.

Any ideas?

Thanks, Ralph

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

[SQL] production parameters

2003-09-13 Thread chester c young 
What is the best source doc for postgresql setup in a production
environment?  I have read
- giving it a big chuck of shmem
- os not marking data files as accessed or modified
but cannot find the doc.

thanks,
Chester

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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

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

Re: [SQL] Q: select query

2003-09-13 Thread Rod Taylor 
> in other words, all but the first row of a group.

Interesting question. The below should work and be quick so long as
there is a UNIQUE(col1, col2) constraint.

SELECT col1
 , col2
  FROM j
 WHERE col2 != (SELECT col2
  FROM j AS jsub
 WHERE col1 = j.col1
  ORDER BY col2 ASC
 LIMIT 1);


signature.asc
Description: This is a digitally signed message part

Re: [SQL] Q: select query

2003-09-13 Thread Manfred Koizar 
On 12 Sep 2003 10:58:45 -0700, [EMAIL PROTECTED] (G. Ralph Kuntz, MD) wrote:
>I would like to select the second and subsequent rows where the first
>column is the same:
>
>   1   b
>   1   c
>   3   f
>
>in other words, all but the first row of a group.

all =   SELECT * FROM t;

but =   EXCEPT

the first row of a group =
SELECT i, min(x) FROM t GROUP BY i;

or (if there are more columns)
SELECT DISTINCT ON(i) * FROM t ORDER BY i, x;

Putting it together:
SELECT i, x FROM t EXCEPT (SELECT i, min(x) FROM t GROUP BY i);

or
SELECT * FROM t EXCEPT (SELECT DISTINCT ON(i) ...);

Servus
 Manfred

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

[SQL] sorting

2003-09-13 Thread chester c young 
how do you set sorting for indicies and order by?  is it set once for
the database, or can it be set per index?


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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

Re: [SQL] Q: insert/update in same statement

2003-09-13 Thread Bruno Wolff III 
On Fri, Sep 12, 2003 at 10:55:17 -0700,
  "G. Ralph Kuntz, MD" <[EMAIL PROTECTED]> wrote:
> Is it possible to make a single SQL statement that will insert a row
> if it does not already exist, otherwise it will just update the
> fields?

No.

> Actually, just a statement that will not give an error about duplicate
> keys would be enough for my application.

This you can do by using a select statement that checks if the primary
key is a duplicate on the insert statement. An example was posted on
one of the lists about a week ago.

Note that there are conncurrency issues with doing this. Either you want
to exclusively lock the table or use serializable isolation and be
prepared to retry after failures.

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

Re: [SQL] production parameters

2003-09-13 Thread Gregory S. Williamson 
You might find some things of use in these:




Greg W.
DBA GLobeXplorer LLC

-Original Message-
From:   chester c young [mailto:[EMAIL PROTECTED]
Sent:   Sat 9/13/2003 11:10 AM
To: sql pgsql
Cc: 
Subject:[SQL] production parameters

What is the best source doc for postgresql setup in a production
environment?  I have read
- giving it a big chuck of shmem
- os not marking data files as accessed or modified
but cannot find the doc.

thanks,
Chester

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.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 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] createlang plpgsql failing on redhatlinux7.2

2003-09-13 Thread Robert Treat 
On Friday 12 September 2003 12:18, Richard Huxton wrote:
> On Friday 12 September 2003 16:49, Robert Treat wrote:
> > rather do an rpm -qa | grep post to see if postgresql-pl-7.3.4-2PGDG is
> > installed, it is the rpm for procedural languages in 7.3.* and didn't
> > exist in the 7.2.* rpmset.
>
> Ah, but the .so isn't in the -pl RPM (I'm guessing plpgsql is considered
> "core").
>
> $ rpm -qif /usr/lib/pgsql/plpgsql.so
> Name: postgresql-serverRelocations: (not relocateable)
> Version : 7.3.4 Vendor: (none)
> ...

Right you are Richard, my apologies. I'll go crawl back under my rock now :-)

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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

   http://archives.postgresql.org

[SQL] A generic trigger?

2003-09-13 Thread ow 
Hi,

Am looking for a way to minimize the amount of fuctions that support triggers.
E.g., there's "company" and "company_backup" tables. Update trigger on the
"company" table will put a record in the "company_backup" table whenever
"company" record is updated.

The problem is that there's quite a few other tables for which similar backup
logic has to be done (e.g. "custormer" and "customer_backup", etc). The backup
logic is the same, only structure of the tables changes.

Is there a way to write a generic trigger/function that would deal with backup
regardless of the table structure?

Thanks in advance.








__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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

   http://archives.postgresql.org

[SQL] Use derived expression in select statement

2003-09-13 Thread Richard Sydney-Smith 
I am converting a database from Sybase and have found the following command
does not work in the same fashion.

select dy_c , dy_sect as tsect, (cy_bfwd + dy_p1T4) as curr_bal from fclitot
where dy_yr = 0 and (curr_bal) <-0.005

In the actual command used curr_bal is calculated from a very much longer
expression and rather than repeat the expression I simply refer to it by its
assigned name in sybase.

In postgres it tells me curr_bal is not found. is their a simular shorthand
available in postgres? curr_bal is calc from about 12 fields :{

thanks

Richard


---(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] Use derived expression in select statement

2003-09-13 Thread Tom Lane 
"Richard Sydney-Smith" <[EMAIL PROTECTED]> writes:
> select dy_c , dy_sect as tsect, (cy_bfwd + dy_p1T4) as curr_bal from fclitot
> where dy_yr = 0 and (curr_bal) <-0.005

> In postgres it tells me curr_bal is not found.

As it should --- this is completely illegal according to the SQL
standard.  It's not even well-defined.  The SQL evaluation model
is that WHERE clause processing is done *before* evaluation of
the select list.

The usual way to avoid writing common subexpressions is to use a
sub-select, for example

select dy_c , dy_sect as tsect, curr_bal
from (select *, (cy_bfwd + dy_p1T4) as curr_bal from fclitot) as ss
where dy_yr = 0 and (curr_bal) <-0.005

This doesn't necessarily save you from evaluating the curr_bal
expression twice, mind you.  It just saves you from writing it out
twice.

regards, tom lane

---(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] [GENERAL] MD5() function not available ??

2003-09-13 Thread Dennis Bjorklund 
On Thu, 11 Sep 2003, Marek Lewczuk wrote:

> I've searched for MD5 crypting function in PG, but I did not find it.
> Anyone knows how to implement this function in PG ?

It's implemented in the pgcrypto module that is in contrib in the tarball. 
Once you have added that you can for example do

dennis=# SELECT encode (digest ('the string', 'md5'), 'hex');
 44d5a3f30f0328e0cf60cd275ed3aac9

-- 
/Dennis


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

   http://archives.postgresql.org