Re: [SQL] Entering data in serial column

[david]

Hi There,

If you omit the column names, the values are assumed to follow the order
of the column names in the table definition. So if you have the columns
with default values at the end of the table definition, then you don't
need to insert a value for them.  Same as in C.

It is better practice though to name the columns, as then if you rebuild
your schema, or alter the table (Effecting the order of the columns) then
the insert statement retains its meaning.

If you have 40 columns, then you are going to have an awfull lot of
trouble maintaining the insert statement anyway.  Whats an extra few lines
of SQL with the column names (they will probably help you keep track of
which one you are up to anyway).


Devrim GUNDUZ <[EMAIL PROTECTED]> Wrote:
> Now, since id is a serial, while inserting data into info I write:
> 
> INSERT INTO info (name,surname,address) VALUES ('$name','$surname',
> '#address');
> 
> Is there a shorter way of doing this? I mean, if I had 40 fields in this
> table, would I have to write all the fields? I mean, is there a syntax
> beginning like
> 
> INSERT INTO info VALUES (

--
David Stanaway



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

http://archives.postgresql.org

Re: [SQL] Linking against null-fields.

[david]

Hi Alex,
For this you need an outer join (Made easy as of postgresql 7.1)

Alexander Deruwe Wrote:
> Hey all,
> 
> I'm sorry if this question came up before, I couldn't find it in the
> archives..
> 
> Suppose I have two tables:
> 
> table File: contains alot of fields + 'driver' field, which refers to
> another
> table. I did not 'reference' it when creating the database because
> null-values have to be possible for this field.
> 
> Now, if in this File-table the field 'driver' is not filled in, the row
> will
> not be included in a query such as this one:
> 
> SELECT f.ID, d.NAME FROM FILE f, DRIVER d WHERE (d.ID = f.DRIVER);

In Postgresql 7.1 You would do this as:

SELECT f.ID, d.NAME
FROM FILE f
LEFT OUTER JOIN DRIVER d ON d.ID = f.DRIVER

This join will fill in the missing rows from DRIVER  with NULL values.

Eg:
FILE:
ID  DRIVER
1   NULL
2   1
3   4

DRIVER:
ID  NAME
1   broken.dll
2   foo.zip


SELECT f.ID, d.NAME
FROM FILE f
LEFT OUTER JOIN DRIVER d ON d.ID = f.DRIVER;
ID  NAME
1   NULL
2   broken.dll
3   NULL

--
David Stanaway


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

Re: [SQL] arrays and polygons

[David]

Thanks to you both that helped enormously, 

Dave


- Original Message - 
From: "Joe Conway" <[EMAIL PROTECTED]>
To: "Tom Lane" <[EMAIL PROTECTED]>
Cc: "David" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, February 13, 2004 4:33 PM
Subject: Re: [SQL] arrays and polygons


> Tom Lane wrote:
> > "David" <[EMAIL PROTECTED]> writes:
> >>INSERT INTO species (location) VALUES ('{((432,89), (45,87), (89,87)),
> >>((54,78), (34,98))};
> > 
> > I think you'd need to double-quote each polygon within the array
> > literal.
> > 
> > '{"((432,89), (45,87), (89,87))", "..."}'
> > 
> > The array parser doesn't think parens are special, so it's not going to
> > magically distinguish array commas from polygon commas for you.
> > 
> > BTW, if you are using 7.4, the ARRAY[] constructor syntax might be
> > easier to use.
> 
> FWIW, here's what it would look like in 7.4.x:
> 
> regression=# select ARRAY['((432,89), (45,87), (89,87))'::polygon, 
> '((432,89), (45,87), (89,87))'];
>  array
> -
>   {"((432,89),(45,87),(89,87))","((432,89),(45,87),(89,87))"}
> (1 row)
> 
> You need to explicitly cast at least the first polygon in order to get 
> an array of polygons (versus an array of text).
> 
> HTH,
> 
> Joe
> 

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

[SQL] searching polygons

[David]

What query would i have to use to search for an item using a polygon as a
parameter? (i.e a very large polygon that would identify smaller polygons
within it) ideally i would like to give postgresq a series of co-ordinates
and then have it return all those results whose polygons fall into that set
of co-ordinates, is this possible?

at the moment all i can think of is


select * from species where location between '(0,0)' and  '(1000,0)' and
'(0, 1000)' and '(1000; 1000)';

I think im way off, any suggestions?

Cheers Dave


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

   http://archives.postgresql.org

Re: [SQL] searching polygons

[David]

Hi elin thanks for advice, i looked at your website but i didnt really
understand the information given, sorry. However i looked at the postgres
documentation and realised what i had to do

select * from species where location[1] @ polygon '((0,0), (1000,0),
(1000,1000), (0,1000))':

This works fine for just the one location ([1]), but when i tried to search
the entire array of polygons using the query:

select * from species where location @ polygon '((0,0), (1000,0),
(1000,1000), (0,1000))':

i got this:

Error: ' and 'polygon'
You will have to retype this query using an explicit cast (State:S1000,
Native Code: 7)

I am not sure how to do this, any suggestions
Many thanks Dave


- Original Message - 
From: "elein" <[EMAIL PROTECTED]>
To: "David" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, February 22, 2004 2:12 AM
Subject: Re: [SQL] searching polygons


> You should use some variation of overlaps or
> contains within.  There is some discussion and
> a list of operators in Issue #61 of General Bits.
> ( http://www.varlena.com/GeneralBits/61 )
>
> I would also suggest looking at the geometric
> operators in the documentation.  You may have
> to cast the polygon to a circle to use the operators,
> but it will still tell you whether the smaller polys
> are contained within or overlap the larger.
>
> elein
>
> On Tue, Feb 17, 2004 at 07:01:51PM -, David wrote:
> > What query would i have to use to search for an item using a polygon as
a
> > parameter? (i.e a very large polygon that would identify smaller
polygons
> > within it) ideally i would like to give postgresq a series of
co-ordinates
> > and then have it return all those results whose polygons fall into that
set
> > of co-ordinates, is this possible?
> >
> > at the moment all i can think of is
> >
> >
> > select * from species where location between '(0,0)' and  '(1000,0)' and
> > '(0, 1000)' and '(1000; 1000)';
> >
> > I think im way off, any suggestions?
> >
> > Cheers Dave
> >
> >
> > ---(end of broadcast)---
> > TIP 6: Have you searched our list archives?
> >
> >http://archives.postgresql.org
>


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

   http://archives.postgresql.org

[SQL] changing constraints

[David]

I tried adding a constraint thus:
de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
But get the msg:
ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2

de4=> \d genus
Table "public.genus"
 Column | Type  | Modifiers
+---+---
 genus_name | character varying(20) | not null
 gender | character varying(10) |
 cas_gen_number | integer   |
 family_name| character(7)  |
Indexes: genus_pkey primary key btree (genus_name)
Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION

I cant see a $2 constraint so why am i getting the error msg?

Many thanks Dave


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

Re: [SQL] changing constraints

[David]

Cheers that worked fine, i guess its obvious im new to postgres (SQL in
general!), oh well you have to learn somehow

Dave

>
> Try something like (untested):
>   ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN
> ('masculine','feminine'));
>
>
> -- 
>   Richard Huxton
>   Archonet Ltd
>


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

[SQL] Alter table

[David]

Ok another very newbie question. How can i change the data type a column can
accept? at the moment it will only take character(7) i want to change it to
varchar(30), but i cant figure how, ideas?

Many thanks Dave


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

[SQL] Changing primary keys

[David]

Is it possible to change the primary key of a relation? I want to add an
attribute, that i already have in the realtion, to the primary key (yes i
realise i designed my model pretty badly)


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

[SQL] arrays and polygons

[David]

Hi there i am having problems inserting an array of polygons into a table, i
have added the column using:

ALTER TABLE species ADD COLUMN location polygon[];

this works fine, but when i try

INSERT INTO species (location) VALUES ('{((432,89), (45,87), (89,87)),
((54,78), (34,98))};

I get the following error message:

ERROR: Bad polygon external representation '((432'

Where am i going wrong, all help is much appreciated DAvid


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

[SQL] table as field type??

[David Diller]

I noticed that it's possible to have a table as a field type.
For example:
create table foo (val1  integer);
create table bar (val2 foo);
although the following doesn't work: create table bar2 (val2 foo[]);

val2 in table bar ends up as an int4, designed for storing an oid from
table foo.
Is there a way to perform a query on bar that will return values from table
foo (i.e., val1)?

Thanks,

David

[SQL] blobs and small binary objects

[David Wall]

I'm new to Postgresql and am trying to figure out how I'd store Serializable
objects, byte arrays and potentially large binary objects.  JDBC has APIs
for getObject(), getBytes() and getBlob(), but how do I define those
attributes inside a table?  I don't see the typical BYTE or BINARY or BLOB
types.

I know that 7.0.3 still suffers from the 8K row limit, so I'd hope that the
JDBC library would automatically handle this for me, if I define some small
objects as byte arrays, and larger objects are blobs.  I saw somewhere a
type BYTEA (byte array?), but I can't find it in the documentation to know
if this is a real type, one of the user defined types that's a common
extension, etc.

Anybody handle these issues yet?

Thanks,
David

[SQL] Re: blobs and small binary objects

[David Wall]

I discovered type OID in the JDBC sample database for BLOBs, but I don't see
it in the documentation.

>From a quick test, it appears that the blob itself is not stored in the row
itself, but is instead an object pointer of some kind.  Is an OID actually a
fixed length field that points to my blob?  Or is it more like a VARCHAR, in
which case it would be better to store it at the end of the row for storage
performance reasons?

David

[SQL] Please don't kill me!

[David Olbersen]

I have two statements that accomplish the same task and I'm trying to decide
which to use. One uses a sub-select, and the other just does a few more joins.
I expect that giving the SELECT statement's themseleves won't get me much help, so 
here is the output of the EXPLAIN query that I ran on both of them. I read
the FAQ on EXPLAIN a bit but I'm still confused.

So could somebody help me understand why it appears as though the first query
will run much faster (?) than the second?

--snip!--

Nested Loop  (cost=81.80..114.17 rows=33 width=68)
  InitPlan
->  Seq Scan on l_portal_statuses  (cost=0.00..22.50 rows=10 width=4)
  ->  Merge Join  (cost=81.80..86.63 rows=3 width=52)
  ->  Merge Join  (cost=59.13..63.43 rows=33 width=44)
  ->  Sort  (cost=22.67..22.67 rows=10 width=28)
   ->  Seq Scan on contacts m  (cost=0.00..22.50 rows=10 width=28)
  ->  Sort  (cost=36.47..36.47 rows=333 width=16)
   ->  Seq Scan on buildings b  (cost=0.00..22.50 rows=333 width=16)
  ->  Sort  (cost=22.67..22.67 rows=10 width=8)
  ->  Seq Scan on contracts c  (cost=0.00..22.50 rows=10 width=8)
  ->  Index Scan using executives_pkey on executives e  (cost=0.00..8.14 rows=10 
width=16)

Merge Join  (cost=174.38..247.30 rows=333 width=76)
  ->  Index Scan using executives_pkey on executives e  (cost=0.00..60.00 rows=1000 
width=16)
  ->  Sort  (cost=174.38..174.38 rows=33 width=60)
  ->  Merge Join  (cost=167.58..173.53 rows=33 width=60)
  ->  Merge Join  (cost=59.13..63.43 rows=33 width=44)
  ->  Sort  (cost=22.67..22.67 rows=10 width=28)
  ->  Seq Scan on contacts m  (cost=0.00..22.50 rows=10 width=28)
  ->  Sort  (cost=36.47..36.47 rows=333 width=16)
  ->  Seq Scan on buildings b  (cost=0.00..22.50 rows=333 width=16)
  ->  Sort  (cost=108.44..108.44 rows=100 width=16)
  ->  Merge Join  (cost=92.50..105.12 rows=100 width=16)
  ->  Sort  (cost=69.83..69.83 rows=1000 width=12)
  ->  Seq Scan on contracts c  (cost=0.00..20.00 rows=1000 
width=12)
  ->  Sort  (cost=22.67..22.67 rows=10 width=4)
  ->  Seq Scan on l_portal_statuses l (cost=0.00..22.50 rows=10 
width=4)
--snip!--

Hopefully that's not too ugly.

TIA

-- Dave

[SQL] Don't want blank data

[David Olbersen]

Greetings,
  Is there a way to have postgresql always return a value for each row
  requested? To be more clear, if I were using a Perl SQL hybrid I would write
  something like

  SELECT computer_ip or 'unset' FROM computers;

  So that if computers.computer_ip is NULL or '' I will get 'unset' back from
  the database. I hope this makes sense and somebody can point me in a good
  direction

-- Dave

Re: [SQL] Change or get currentdb

[David Olbersen]

>From \?
 \c[onnect] [dbname|- [user]]
 connect to new database (currently '')

so typing "\c" gives you the database you're currently connected to and
"\c " would connect you to that database.


On Thu, 25 Jan 2001, Sergiy Ovcharuk wrote:

->How can I change and/or get to know a current db name using sql script in
->PostgreSQL?

-- Dave

[SQL] PL/PGSQL function with parameters

[David Richter]

Folks,

I wrote that function, wich doesn't work. I want to hand over the name
of the tables(relation_table, update_table) and a
column(column_to_fill). The intention is, to use the function also with
other tables(not hard coded). 

BUT this error appears :
psql:restructure.sql:32: ERROR:  parser: parse error at or near "$1"

I didn't found any solution. 
I would be grateful , if I could get some more Examples(more than in the
Docu of www.postgresql.org and Bruce Monjiam's Book) about parameters in
PL/PGSQL - functions.
I would be no less grateful if anybody give detailed suggestions.

CREATE FUNCTION patient_study_restructure (text,text,text) RETURNS
integer AS '
DECLARE 

relation_table ALIAS FOR $1;
update_table ALIAS FOR $2;
column_to_fill ALIAS FOR $3;
psr_rec record;
bound integer;
i integer := 0;

BEGIN 
FOR psr_rec IN SELECT * FROM relation_table LOOP
UPDATE update_table 
SET column_to_fill = psr_rec.parentoid
WHERE chilioid = psr_rec.childoid;
i := i + 1;
END LOOP;
IF NOT FOUND THEN RETURN 1; 
ELSE RETURN i;
END IF;
END;

' LANGUAGE 'plpgsql';

SELECT
patient_study_restructure('relpatient_study000','study','patientoid');


Anybody (Jan Wieck?) who can make some sugestions on
the above will
receive my enthusiastic gratitude.

David

Re: [SQL] SQL Join - MySQL/PostgreSQL difference?

[David Olbersen]

On Thu, 1 Feb 2001, Brice Ruth wrote:

->SELECT
->  a.Number,
->  a.Code,
->  a.Text
->FROM
->  b,
->  a
->WHERE
->  (b.Id = a.Id) AND

These next two statements are very ambiguous. Make them explicit as you have
with "(b.Id = a.Id)" and "(b.d_Id = 'key3')"

Also, be sure that 'key3' is how what you want looks in the database

->  (VersionId = 'key1') AND
->  (Category = 'key2') AND
->  (b.d_Id = 'key3')
->ORDER BY
->  a.Number;

Also, make sure ( '\d b' ) that your columns are case-sensatively named 'Id' and
such as this does matter.

-- Dave

[SQL] Data Types

[David Olbersen]

Hello.

I have a table in which I'm trying to store the length of a sound file. I
decided to use the TIME data type. Was this correct? One of the operations I
want to do is sum() all of my files lengths to get the total amount in terms of
time, of sound that I have.

I notice that sum() doesn't take a TIME argument, so I cast it to an interval
as such:
  SELECT SUM( length::interval ) FROM songs;

However this gives me output that I don't know how to read: '7 02:34:27'
Does that read as 7 Days, 2 Hours, 34 Minutes and 27 seconds?

TIA

-- Dave

Re: [SQL] Temp Tables & Connection Pooling

[David Olbersen]

On Fri, 2 Mar 2001, Gerald Gutierrez wrote:

->Recently I wanted to implement Dijkstra's algorithm as a stored procedure,
->and finding that PL/PGSQL cannot return record sets, I thought about using
->a temporary table for the results. If tempoary tables are session-specific,
->however, then wouldn't connection pooling make it unusable since the table
->might "disappear" from one query to the next? What are alternative
->approaches to implementing Dijkstra's algorithm inside the database?



Wouldn't a VIEW do what you want?



-- Dave


---(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] Two way encryption in PG???

[David Olbersen]

On Sun, 4 Mar 2001, Boulat Khakimov wrote:

->How do I encrypt/decrypt something in PG?

Perhaps it'd be better to one-way encrypt something? Granted I don't know the
details of your project, but allowing a way to "decrypt" something is rather
insecure.

-- Dave


---(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] How do I use text script containing SQL?

[David Olbersen]

On Mon, 5 Mar 2001, Jeff S. wrote:

->I want to be able to use the file to create my table.
->I've tried psql -d databasename -e < filename.txt
->but that doesn't work.

You're making it too dificult :-)
'psql -d databasename < filename.txt' should work just fine

-- Dave


---(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] explain EXPLAIN?

[David Olbersen]

Hello,
  I'm looking for a better tutorial of how EXPLAIN works. I know Mr. Tom Lane
  wrote a "quick & dirty explanation" and that "plan-reading is an art that
  deserves a tutorial, and I haven't had time to write one". In which case I'd
  like to know if there's any other tutorials/resources.

  I think I get the jist of it (an index scan is better than a seq scan?) but
  I'd like to read more. Does anybody have any suggestions?

-- Dave


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

[SQL] Optimization via explicit JOINs

[David Olbersen]

Greetings,
  I've been toying aroudn with postgres 7.1beta5's ability to control the
  planner via explicitely JOINing tables. I then (just for giggles) compare the
  difference in the EXPLAIN results.

  I'm no super-mondo-DBA or anything, but in my two attempts so far, the numbers
  I get out of EXPLAIN have been about 1/2 as small.

  Below are two EXPLAIN results, am I correct in reading that one is indeed
  "twice as fast" as the other? I say twice as fast because the top-most cost in
  the first query is 58.62, but in the second one it's only 32.09. Am I reading
  this correctly?

-- First EXPLAIN --
Sort  (cost=58.62..58.62 rows=14 width=60)
  ->  Nested Loop  (cost=0.00..58.35 rows=14)
->  Nested Loop  (cost=0.00..29.99 rows=14)
  ->  Seq Scan on playlist p  (cost=0.00..1.61 rows=14)
  ->  Index Scan using songs_pkey on songs s (cost=0.00..2.01 rows=1)
->  Index Scan using artists_pkey on artists a (cost=0.00..2.01 rows=1)


-- Second EXPLAIN --
Sort  (cost=32.09..32.09 rows=1)
  ->  Nested Loop  (cost=0.00..32.08 rows=1)
->  Nested Loop  (cost=0.00..30.06 rows=1)
  ->  Seq Scan on playlist p  (cost=0.00..1.61 rows=14)
  ->  Index Scan using songs_pkey on songs s (cost=0.00..2.02 rows=1)
->  Index Scan using artists_pkey on artists a  (cost=0.00..2.01 rows=1)

-- Dave


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

Re: [SQL] Optimization via explicit JOINs

[David Olbersen]

On Fri, 9 Mar 2001, Stephan Szabo wrote:

->Not entirely.  Those are only estimates, so they don't entirely line up
->with reality.  Also, I notice the first estimates 14 rows and the second
->1,  which is probably why the estimate is higher.  In practice it probably
->won't be significantly different.

So really I'm just getting back estimations of cost and rows returned?
Incidentally, both queries returned the same data set, that's a Good Thing (tm).

-- Dave


---(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] Optimization via explicit JOINs

[David Olbersen]

On Fri, 9 Mar 2001, Stephan Szabo wrote:

-> Hmm, what were the two queries anyway?

The "slower" query

SELECT
  to_char( p.insertion_time, 'HH:MI AM MM/DD' ) as time_in,
  s.nameas title,
  a.nameas artist,
  s.length  as length
FROM
  playlist p,
  songss,
  artists  a
WHERE
  p.waiting   = TRUE  AND
  p.song_id   = s.song_id AND
  s.artist_id = a.artist_id
ORDER BY p.item_id

The "faster" query

SELECT
  to_char( p.insertion_time, 'HH:MI AM MM/DD' ) as time_in,
  s.nameas title,
  s.length  as length,
  a.nameas artist
FROM
  playlist p JOIN songs s USING (song_id),
  artists  a
WHERE
  p.waiting   = TRUE  AND
  p.song_id   = s.song_id AND
  s.artist_id = a.artist_id
ORDER BY p.item_id;

Notice how the only difference is in the FROM clause?
-- Dave


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

Re: [SQL] Optimization via explicit JOINs

[David Olbersen]

On Fri, 9 Mar 2001, Stephan Szabo wrote:

->As a question, how many rows does
->select * from playlist p join songs s using (song_id) where
->p.waiting=TRUE;
->actually result in?

Well it depends. Most of the time that playlist table is "empty" (no rows where
waiting = TRUE), however users can (in a round about way) insert into that
table, so that there could be anywhere from 10, to 2,342, to more.

Why do you ask?

(The reason those plans chose 14 was because, at the time, there were 14 rows in
playlist)

-- Dave


---(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] Comparing dates

[David Lynn]

Hello -

It seems that using BETWEEN would work well, especially for finding
dates between two other dates.

WHERE date_date BETWEEN '03-02-2001'::date and '03-03-2001'::date

--d

> On Tue, 6 Mar 2001, Markus Fischer wrote:
> 
> > Hello,
> >
> > I've a SELECT statement on many joined Tabled and one of them has
> > a date column called 'date_date'. When I fetch a date e.g.
> > '02-03-2001', I get, say, 60 results back. When I now perform the
> > same query with another date, lets take '03-03-2001', I get back
> > about 70 results.
> >
> > When I now modify my query to get both results in one I write
> >
> > SELECT
> >   
> > FROM
> >   ..
> >   AND
> >   date_date >= '2001-03-02'
> >   AND
> >   date_date <= '2001-03-03'
> >   AND
> >   
> >
> > I think I should get back the rows for both days, 60 + 70 makes
> > 130 to me. But what I get back is even smaller then 60. I
> > allready tried TO_DATE conversion, an OR construct but always
> > the same result.
> >
> > Is there something special to know when comparing/working with
> > date-datetypes ?
> >
> >
> > kind regards,
> >   Markus
> >
> > --
> > Markus Fischer,  http://josefine.ben.tuwien.ac.at/~mfischer/
> > EMail: [EMAIL PROTECTED]
> > PGP Public  Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc
> > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1  C2B5 D674 B445 C227 2BD0
> >
> > ---(end of broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> >
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

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

[SQL] error joining 2 views containing GROUP BYs

[david morgan]

Dear Friends,
I am having a few problems using a select query which joins two views
containing aggregate functions (see query 1 below).  Entering this query in
MS Access through ODBC (linking the tables in) I get the correct result of:

uidtotalansweredcorrecttotaltimeweekno
22152275
22352245

Using Psql I get the result:

 uid | totalanswered | correct | totaltime | weekno
-+---+-+---+
 221 |10 |  10 |54 |  5
 223 |10 |  10 |48 |  5
(2 rows)

I have read similar posts where people have had problems with views
containing aggregate functions so I realise that there remains work to be
done on this aspect, but my question is:
Does anyone know how I can get this query to work?  Can I make the query be
interpreted in the same way as ODBC?
Is the problem the same as
http://www.postgresql.org/mhonarc/pgsql-sql/2000-11/msg00175.html which Tim
Lane explained the problem "the rewriter effectively
expands them in-line" (Tim Lane)?

Any help or tips would be greatly appreciated.

David Morgan.



drop table Users;
create table Users
(
UID   int4 PRIMARY KEY,
Name  text,
Address   text,
TelNo text,
EmailAddress  text,
FavClub   text,
DOB   date,
Password  text,
Language  text
);

drop table QuAnswered;
CREATE TABLE "quanswered" (
"uid" int4 DEFAULT 0 NOT NULL,
"qid" int4 DEFAULT 0 NOT NULL,
"aid" int4 DEFAULT 0,
"tstamp" timestamp DEFAULT "timestamp"('now'::text),
"ttaken" float4,
PRIMARY KEY ("uid", "qid")
);

drop table Questions;
CREATE TABLE "questions" (
"qid" int4 DEFAULT 0 NOT NULL,
"aid" int4 DEFAULT 0,
"queng" text,
"quwel" text,
"weekno" int2 DEFAULT 0,
PRIMARY KEY ("qid")
);

INSERT INTO "users" VALUES (221,'james stagg','23 manai
way\015\012cardiff','029
20315273','[EMAIL PROTECTED]','cardiff','1974-04-15',NULL,'english');
INSERT INTO "users" VALUES (223,'jim','mill
lane','sdkfj','asdgl','rhymmny','199
5-10-01',NULL,'english');

INSERT INTO "questions" VALUES (201,936,'Against which country did Neil
Jenkins
win his first Welsh cap?','201. Yn erbyn pa wlad yr enillodd Neil Jenkins ei
gap
 cyntaf dros Gymru?',5);
INSERT INTO "questions" VALUES (202,366,'Who beat Fiji in the Quarter Finals
of
the 1987 World Cup?','202. Yn erbyn pa wlad y collodd Ffiji yn Rownd Wyth
Olaf C
wpan y Byd 1987?',5);
INSERT INTO "questions" VALUES (203,26,'From which club did Pat Lam join
Northam
pton?','203. I ba glwb yr oedd Pat Lam yn chwarae cyn iddo ymuno gyda
Northampto
n?',5);
INSERT INTO "questions" VALUES (204,821,'In which country was Japan`s scrum
half
 Graeme Bachop born?','204. Ym mha wlad y ganwyd mewnwr Siapan, Graeme
Bachop',5
);
INSERT INTO "questions" VALUES (205,369,'Who is Scotland`s most capped
outside h
alf?','205. Enwch y chwaraewr sydd wedi ymddangos yn safle`r maswr i`r Alban
y n
ifer fwyaf o weithiau?  ',5);


INSERT INTO "quanswered" VALUES (221,201,936,'2001-03-07 10:43:09+00',6);
INSERT INTO "quanswered" VALUES (221,202,366,'2001-03-07 10:43:20+00',8);
INSERT INTO "quanswered" VALUES (221,203,785,'2001-03-07 10:47:15+00',6);
INSERT INTO "quanswered" VALUES (221,204,589,'2001-03-07 10:47:21+00',2);
INSERT INTO "quanswered" VALUES (221,205,257,'2001-03-07 10:47:29+00',5);
INSERT INTO "quanswered" VALUES (223,201,375,'2001-03-07 10:48:14+00',7);
INSERT INTO "quanswered" VALUES (223,202,544,'2001-03-07 10:48:22+00',4);
INSERT INTO "quanswered" VALUES (223,203,26,'2001-03-07 10:48:30+00',6);
INSERT INTO "quanswered" VALUES (223,204,972,'2001-03-07 10:49:42+00',3);
INSERT INTO "quanswered" VALUES (223,205,369,'2001-03-07 10:49:55+00',4);




DROP VIEW all_ans;
CREATE VIEW all_ans as
SELECT  qa.uid, sum(qa.ttaken) as TotalTime, count(qa.aid) as TotalAnswered,
qu.
weekno
FROM quanswered qa, questions qu
WHERE qa.qid=qu.qid
GROUP BY qa.uid, qu.weekno;

DROP VIEW cor_ans;
CREATE VIEW cor_ans AS
SELECT qa.uid, count(qa.uid) AS correct, qu.weekno
FROM questions qu, quanswered qa
WHERE ((qu.aid = qa.aid)
AND (qu.qid = qa.qid))
GROUP BY qa.uid, qu.WeekNo;

Query 1
---

SELECT all_ans.uid, all_ans.totalanswered, cor_ans.correct,
all_ans.totaltime, all_ans.weekno
FROM all_ans, cor_ans
WHERE all_ans.weekno= cor_ans.weekno
AND   all_ans.uid=cor_ans.uid;






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

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

Re: [SQL] error joining 2 views containing GROUP BYs

[david morgan]

Thanks for your help.
It runs blindlingly fast under beta5 so I'll keep my fingers crossed and
hope it will be stable enough!

Tom Lane wrote in message <[EMAIL PROTECTED]>...
>"david morgan" <[EMAIL PROTECTED]> writes:
>> I am having a few problems using a select query which joins two views
>> containing aggregate functions (see query 1 below).
>
>I don't think there's any chance of making that work in pre-7.1
>Postgres.  Sorry :-(.




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Select very slow...

[David Olbersen]

On Sun, 18 Mar 2001, Fernando Eduardo B. L. e Carvalho wrote:

>   select  p.city,count(*) from sales s, person p where s.doc = p.doc
> group by p.city;
>
>Anyone help-me?

1: VACUUM ANALYZE sales
   VACUUM ANALYZE person;

2: That 'count(*)' is going to be slow.
   Try counting a column that's indexed (p.doc might work?)

3: EXPLAIN ;
   That should give you some hints on what to optimize.

-- Dave


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

http://www.postgresql.org/search.mpl

[SQL] Self-Referencing

[David Olbersen]

Hello,

  I have a feeling this isn't going to make much sense, but I'm gonig to try
  anyway.

  What I'd like to do is be able to refer to an outer-SELECT from an
  inner-SELECT. I hope this makes sense.

  I need to be able to refer to the row that's being processed in a SELECT. I'm
  going to use the idea of 'this' referring to the row that's currently being
  processed. Here's the example of what I'd like:

  SELECT
building_id,
num_buildings,
(
  SELECT count( building_id )
  FROM   building_portals
  WHERE  building_id = THIS.building_id
)
  FROM buildings;

  Am I making things too complicated, and if so will somebody *PLEASE* tell me
  the easier way to do this. Thanks.

-- Dave


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Inserting binary data (BLOBs) in v7.1

[David Lizano]

Has somebody insert binary data  (BLOBs) in a row in PostgreSQL v7.1?

Is the correct data type to do it "VARCHAR(65535)"?



---(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] pg_dumpall and password access

[David Lizano]

At 19.29 3/4/01 -0400, you wrote:
>Christophe Labouisse <[EMAIL PROTECTED]> writes:
> > I'm trying to run pg_dumpall to backup all my users' bases but since I
> > have configure pg_hba.conf to "passwd" pg_dumpall always fails:
>
>pg_dumpall doesn't work very well with password authentication (and
>even if it did, storing the password in a cron script doesn't seem
>like a good idea to me).

 From the cron script you can execute somethin like this.

 su -l postgres -c pg_dumpall 

and then, "pg_dumpall" will be executed by the postgres user.


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

[SQL] DROP TABLE in transaction

[David Olbersen]

Hello.

I was wondering if anybody could explain to me why I can't roll back dropping a
table. I would think that of all the events that should be rollback-able,
dropping a table would be the first on the list.

-- Dave


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

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

Re: [SQL] DROP TABLE in transaction

[David Olbersen]

On Thu, 12 Apr 2001, Peter Eisentraut wrote:

> Because DROP TABLE removes the table file on disk, and you can't roll back
> that.  Actually, in 7.1 you can.  ;-)

Well I understand that it's being taken from the disk, but why does that action
have to be done *right now*?
Why can't it be postponed until I type 'commit;' ?

I wonder how much time this addition would have saved those of us who type
quickly and use the tab-completion too much :)

-- Dave


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

Re: [SQL] is this proper sql?

[David Olbersen]

On Tue, 17 Apr 2001, clayton cottingham wrote:

> now i personally dont think this is real sql
> anyone?

Nope, not real. Although that type of syntax would be handy IMHO.

-- Dave


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

Re: [SQL] index/join madness

[David Olbersen]

On Wed, 23 May 2001, Michael Richards wrote:

> Finally, I'm planning on moving this to 7.2 and converting all the
> joins to use outer joins. Will there be a significant penalty in
> performance running outer joins?

Why are you planning on using outer joins? Yes there is a performance penalty
because postgres will have to emit more tuples. Are you sure that you need to
use outer joins?

-- Dave


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

[SQL] binary data

[David Lizano]

Is there any method to insert binary data in a PostgreSQL row?

What data type must I use to insert an image?

Thanks in advance.


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

http://www.postgresql.org/search.mpl

[SQL] Problem with pg_index.

[David BOURIAUD]

Hi the list !
I'm new to this list.
I've tried to write a sql query to get all the fields of a table and to
have mentionned is a field can be null or not, and if a field is a key
or not.
Before showing the query I send, let me tell you that I'm running
postgre v7.1.2 on a SuSE Linux 6.4 box, and that everything works fine
but this query.
Here it is :

select 
pg_class.oid, 
pg_attribute.attname, 
pg_attribute.attbyval, 
pg_attribute.attnotnull 
from 
pg_class, 
pg_attribute, 
pg_index 
where 
relname='essai2'
and pg_attribute.attrelid=pg_class.oid 
and pg_attribute.attnum >0 
and pg_index.indrelid=pg_class.oid 
and pg_index.indkey[0] = pg_attribute.attnum 

This query doesn't work, the back-end answers Error : Invalid Command
name "0"...
What now ? I've checked out the online doc, and the archives of this
list (that's where I got pg_index.indkey[0] from), but nothing seems to
work, since pg_index.indkey seems to be an int2vector, and I found no
integrated function to check if a value is in this vector... Thanks for
your ideas and//or help.
-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[Fwd: [SQL] Problem with pg_index.]

[David BOURIAUD]

-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021


Tom Lane wrote:
You are totally right !

> 
> I don't think so: there is no such error string anywhere in the PG
> sources.  (Also, when I try the query, it seems to work fine.)
> 
> However, I find the following possibly relevant match in the PGAccess FAQ:
> 
> 8. I am receiving the following error: message invalid command
> name "namespace" while executing "namespace eval Mainlib  ..."
> That means 100% that you have an older version of Tcl/Tk that
> don't recognize namespaces command. Please upgrade to Tcl/Tk 8.0.x 
>minimum
> 
> Are you using Tcl?  If so, I'd bet the problem is on the client side.
Indeed I use pgaccess, and when I type the query under psql, it works
fine, so I'm downloading the sources of tcl8.3.3 and upgrade as soon as
possible.
Yet, I have a problem with complex keys, I can't check pg_index[0] on
every row fetched, so, how can I do ? I've tried to add arrays
extentions, but the *= operator doesn't work with int2vector How can
I do then ? Thanks anyway !
Have a nice week-end.
-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021




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

http://www.postgresql.org/search.mpl

[SQL] How to use the type int2vector ?

[David BOURIAUD]

Hi the list !
How can I make a test on a int2vector ? If I have a vector that contains
(1, 2, 5, 9), how can I check these values incivicually ? Thanks by
advance for your help. 
-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021

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

http://www.postgresql.org/search.mpl

Re: [SQL] How to use the type int2vector ?

[David BOURIAUD]

David BOURIAUD wrote:
> 
> Hi the list !
> How can I make a test on a int2vector ? If I have a vector that contains
> (1, 2, 5, 9), how can I check these values incivicually ? Thanks by
> advance for your help.
I answer myself by posting another question... Is there a way to know
from the system tables the keys of a table ? If there is only one key,
it is not hard to know, but when there are more than one, how can I do ?
Thanks by advance.

-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021

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

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

[SQL] About i8n

[David BOURIAUD]

Hi the list !
I principally use postgres with php, but I think that for my question,
it remains the same... Is there a way to have all error messages
returned by the back-end translated into french or another language ? If
so, how to set it up ? Thanks by advance.
-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021

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

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

[SQL] About table column names.

[David BOURIAUD]

Hi the list !
As far as I know, column names for a table can't contain any space,
tabs, and other sort of "exotic" characters. Is there a way to add a
description of a table column anywhere in postgres tables, or does it
have to be handled manually by creating a custum table handling this
kind of datas ? Thanks by advance for any suggestion.
-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021

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

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

Re: [SQL] Re: About i8n

[David BOURIAUD]

"J.H.M. Dassen (Ray)" wrote:
> 
> David BOURIAUD <[EMAIL PROTECTED]> wrote:
> > Is there a way to have all error messages returned by the back-end
> > translated into french or another language ?
> 
> http://www.de.postgresql.org/devel-corner/docs/postgres/nls.html
Thanks, but it doesn't help in any way ! It tells how to have a client
program have nls support, but not postgres ! I'm running postgreSQL
v7.1.2, and neither found any *.po, *.mo or *.mk files in the tree
directory of either the sources or the bianries. Furthermore, when I
type gmake init-op, gmake complains that there is no rule to make
init-po. Well, well, well, not so good. By the way, I can speak english,
so it's not a problem for me, but I think of my users ! Thanks again,
for I learned something consulting theses pages anyway.
-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Memory exhausted

[David Richter]

Hello!

I used a self written funtion in plpgsql with a database  of 2 Gigabyte
size. My server has 384 Megabytes of RAM.

So I got this error by calling the following function:

psql:restructure.sql:139: FATAL 1:  Memory exhausted in AllocSetAlloc()
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:restructure.sql:139: connection to server was lost

In the memory usage program Its shown that the function needs all the
memory.


The function fetches all XXX rows of a table
and writes a value to another table

CREATE FUNCTION series_image () RETURNS integer AS '
DECLARE 

psr_rec record;
i integer := 0;

BEGIN 
FOR psr_rec IN SELECT * FROM relseries_image000 LOOP
UPDATE image 
SET seriesoid  = psr_rec.parentoid
WHERE chilioid = psr_rec.childoid;
i := i + 1;
END LOOP;
IF NOT FOUND THEN RETURN -1; 
ELSE RETURN i;
END IF;
END;

' LANGUAGE 'plpgsql';

What could I optimize in this function above?
I tried the Select statement in the psql command and it has taken 20
minutes. I estimate that there are more than 40 rows in the table.
Then it breakes , the announcment appears: malloc: Resource temporarily
unavailable
and psql is crashed.

Should I change the postmaster parameters?
actually they are :
./postmaster -i -S -D/usr/local/pgsql/data -B 256 -o -e -o -F

What can I do?
Thanks in advance for any advice

David

begin:vcard 
url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A
n:M. Richter;David
x-mozilla-html:FALSE
org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik
version:2.1
email;internet:[EMAIL PROTECTED]
adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany  ;Heidelberg;Germany;;
x-mozilla-cpt:;-15296
fn:David M. Richter
end:vcard



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] listing foreign keys

[David Brown]

is there any way to view the existing foreign keys in a database schema?

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] indexing arrays in pgaccess's query interface is failing

[David Stanaway]

Hi there, I am having some difficulties with using arrays in pgaccess 
relating to arrays.

Here is an example schema:

CREATE TABLE arraykeys (
akID int,
akName varchar(12),
PRIMARY KEY(akID)
);

CREATE TABLE items (
itID serial,
itProperties bool[],
PRIMARY KEY(itID)
);

--.. And some Data

INSERT INTO arraykeys VALUES (1,'Active');
INSERT INTO arraykeys VALUES (2,'Overdue');
INSERT INTO arraykeys VALUES (3,'Local');

INSERT INTO items (itProperties) VALUES ( '{1,0,1}');
INSERT INTO items (itProperties) VALUES ( '{0,1,1}');



--.. And now the query that I am having problems with.

SELECT itID, itProperties[akID], akName
FROM items, arraykeys;



In the readline client psql, the above select statement works perfectly
scratch-# FROM items, arraykeys;
  itid | itproperties | akname
--+--+-
 1 | t| Active
 1 | f| Overdue
 1 | t| Local
 2 | f| Active
 2 | t| Overdue
 2 | t| Local
(6 rows)


However

In pgaccess,
when I try to execute the same query in query builder,
I get the tcl error dialogue:
Error: invalid command name "akID"


Is there an alternate way indexing arrays in queries that I should be 
using?
Or is pgaccess just not suitable for this class of queries!


--
Best Regards
David Stanaway
.-
Technology Manager  -  Australia's Premier Internet Broadcasters
[EMAIL PROTECTED] Office +612 9357 1699
'-

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

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

[SQL] cumulative sum in aggregate query.

[David Stanaway]

Hi there,

I have a query that gives me x/y data for a graph, and at the moment, 
the y data is relative.

EG:

x   |  y
1.2 | +1
1.9 | 0
3.4 | +4
5.2 | -2
6.7 | -1
9.3 | 0
11.3| -1

Now, I want to convert this result into a result like this:
x   |  y
1.2 | 1
1.9 | 1
3.4 | 5
5.2 | 3
6.7 | 2
9.3 | 0
11.3| 1

Does anyone have any suggestions as to how to do this?



--
Best Regards
David Stanaway
.-
Technology Manager  -  Australia's Premier Internet Broadcasters
[EMAIL PROTECTED] Office +612 9357 1699
'-

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

http://www.postgresql.org/search.mpl

Re: [SQL] example of [outer] join

[David Stanaway]

 On Friday, July 20, 2001, at 08:22  PM, Gary Stainburn wrote:

My view so far is:
CREATE view member_dets as
select *,
getphone(m.mphone) as phone,
getphone(m.mfax) as fax,
getphone(m.mmobile) as mobile,
getunitno(m.mid) as munitno
from members m,
address a,
teams t,
emails e
where m.madd = a.aid and
m.memail = e.eid and
m.mteam = t.tid;

Try

CREATE VIEW member_dets AS
SELECT *,
getphone(m.mphone) AS phone,
getphone(m.mfax) AS fax,
getphone(m.mmobile) AS mobile,
getunitno(m.mid) AS munitno
FROM members m
-- every member has an address
JOIN  address a ON m.madd = a.aid
-- not everyone has an email address
LEFT JOIN emails e ON m.memail = e.eid
-- every member belongs to a team
JOIN team t ON m.mteam = t.tid;

You will need postgresql 7.1 for this syntax, otherwise, refer to the docs on how do do outer joins using unions (Pretty messy esp if you have more than one...  One very good reason to upgrade to 7.1 if you are still using an earlier version of postgres)

==
David Stanaway
Personal: [EMAIL PROTECTED]
Work: [EMAIL PROTECTED]

Re: [SQL] nextval on insert by arbitrary sequence

[David Stanaway]

Have you looked at the serial type?

This type creates an explicity sequence with a predictable name:
tblname_rowname_seq

and has a default value that selects the next val from that sequence.
You can get the value of the most recently inserted row in your session 
with
CurrVal('tblname_rowname_seq')

Hope this helps :)

On Saturday, July 21, 2001, at 10:04  AM, Dado Feigenblatt wrote:

> Josh Berkus wrote:
>
>> Dado,
>>
>> Maybe we're looking at this the hard way.  Have you thought of simply
>> putting in a DATETIME column and sorting by that?
>>
>> -Josh
>>
> Sorting? I might have expressed myself wrong.
> I'm not concerned about sorting.
> I'm concerned about giving rows in a single table an unique, sequential 
> numbered ID
> based on a sequence per project (as in a serial counter, as in 'create 
> sequence specific_project_sequence;')
> e.g.
> rows related to project A get a number from sequence A
> rows related to project B get a number from sequence B
>
> Is it clear now, or is it me who's not understanding what you're saying?
>
> Sorry for the confusion.
>
>
>
>
>
> -- Dado Feigenblatt Wild Brain, Inc.   
> Technical Director   (415) 553-8000 x???
> [EMAIL PROTECTED]   San Francisco, CA.
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>

--
Best Regards
David Stanaway

Technology Manager
Australia's Premier Internet Broadcasters
Phone: +612 9357 1699
Fax: +612 9357 1169
Web: http://www.netventures.com.au
Support: [EMAIL PROTECTED]

The Inspire Foundation is proudly supported by Net Ventures through the 
provision of streaming solutions for it's national centres.  The Inspire 
Foundation is an Internet-based foundation that inspires young people to 
help themselves, get involved and get online. Please visit Inspire at 
http://www.inspire.org.au


---(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] Are circular REFERENCES possible ?

[David Lizano]

>
>
>Which leads to :
>
>CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer 
>REFERENCES customers, ...)

You can't reference to a table who doesn't exists still.


>CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop 
>integer REFERENCES shops, ...)

Perhaps you can do it something like that if:
1.- Create the two tables.
2.- Use alter table to add the constraint "references".



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

Re: [SQL] Table Constraints with NULL values

[David Allardyce]

I don't mean to re-hash an argument that has been debated-to-death before,
but I can't help myself...

> > However, shouldn't any values that are not NULL violate the constraint
if
> > the same values exist already?
>
> No.  Postgres is conforming to the SQL standard in this.  SQL92 saith
> in section 4.10:
>
>  A unique constraint is satisfied if and only if no two rows in
>  a table have the same non-null values in the unique columns. In
>  addition, if the unique constraint was defined with PRIMARY KEY,
>  then it requires that none of the values in the specified column
or
>  columns be the null value.

I am not sure the standard supports your statement.  Apparently only the
PRIMARY KEY constraint may not contain null values, otherwise its a
violation if "..two rows... have the same non-null values..."

> There's a slightly different statement in the definition of the UNIQUE
> predicate, section 8.9:
>
>  2) If there are no two rows in T such that the value of each
column
> in one row is non-null and is equal to the value of the cor-
> responding column in the other row according to Subclause 8.2,
> "", then the result of the  cate> is true; otherwise, the result of the 
> is false.
>
> AFAICT the intent is that all the comparison columns must be non-null
> (and equal) for two rows to be considered to violate the uniqueness
> constraint.

If 'each' means 'every' then I can see how you come to that conclusion.
However, AFAICT the intent is to say that all rows in a table can be said to
be unique if none of the non-null columns contains equal values in the
corresponding column of another row.  If I use my definition then two all
null rows will not violate the constraint.  If section 8.9 says that every
column must be NON-NULL, then any row that contains a NULL column will
always be in violation of uniquness.

> The standard *does* use the concept that you are after: section
> 3.1 Definitions saith
>
>  h) distinct: Two values are said to be not distinct if either:
> both are the null value, or they compare equal according to
> Subclause 8.2, "". Otherwise they are
> distinct. Two rows (or partial rows) are distinct if at least
> one of their pairs of respective values is distinct. Otherwise
> they are not distinct. The result of evaluating whether or not
> two values or two rows are distinct is never unknown.
>
>  i) duplicate: Two or more values or rows are said to be
duplicates
> (of each other) if and only if they are not distinct.
>
> which is terminology that they carefully refrain from using in defining
> uniqueness constraints.  Had they meant what you want the behavior to
> be, ISTM they'd have defined uniqueness constraints by saying "all the
> rows must be distinct".
>
Actually, I don't think the definition of distinct is a contradiction at
all.  Instead, I think it explains why they specifically exclude NULL values
from the uniqueness comparison.  If they had defined uniqueness as "all rows
must be distinct" then two all null rows would violate the uniqueness
constraint.  Not the behavior I want at all.

David Allardyce
-
  ISTM is the only one I couldn't figure out.




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

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

[SQL] Table Constraints with NULL values

[David Allardyce]

It appears that Postgres will allow any INSERT, despite a multiple-column
constraint, if any of the values INSERTed are NULL.  If I read the included
excerpt correctly (there are like three negatives in the second sentence,
sheesh :) ), multiple NULL values for a column are acceptable or, in other
words, are not a violation of UNIQUEness.

However, shouldn't any values that are not NULL violate the constraint if
the same values exist already?

As an example, into the table definition at the bottom of this message...
   This should be acceptable.
  INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES (NULL,
NULL, NULL, NULL, NULL);
  INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES (NULL,
NULL, NULL, NULL, NULL);

  But this should not...
  INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('Hit', 1, -1, -1, 91);
  INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('Hit', 1, -1, -1, 91);
  ERROR:  Cannot insert a duplicate key into unique index unique_aofunction

  Why does this succeed?
  INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('TauntNPC', 1, NULL, NULL, NULL);
  INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('TauntNPC', 1, NULL, NULL, NULL);

-- Excerpt from the Postgres CREATE TABLE documentation ---
...
UNIQUE Constraint
...
The column definitions of the specified columns do not have to include a NOT
NULL constraint to be included in a UNIQUE constraint. Having more than one
null value in a column without a NOT NULL constraint, does not violate a
UNIQUE constraint. (This deviates from the SQL92 definition, but is a more
sensible convention. See the section on compatibility for more details.)
...
--- End of Excerpt --

CREATE TABLE ao_functions (
id   SERIAL CONSTRAINT funckey PRIMARY KEY,
name   CHARACTER(25),
skill INTEGER NULL,
arg1CHARACTER VARYING(100) NULL DEFAULT NULL,
arg2CHARACTER VARYING(100) NULL DEFAULT NULL,
arg3CHARACTER VARYING(100) NULL DEFAULT NULL,
CONSTRAINT unique_aofunction UNIQUE (name, skill, arg1, arg2, arg3)
);

David Allardyce


---(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] URGENT: restoring a database

[David Stanaway]

On Friday, October 26, 2001, at 11:22  AM, Oleg Lebedev wrote:

> Hi,
> I think I got a problem here.
> I tried to restore my database from dump.bac file, which was created
> with
> pg_dumpall -o > dump.bac
> This is what I did:
>> createdb replica
>> psql -d replica -f dump.bac
>

Sounds like you didn't read up on pg_dump to closely.

What you probaly should have doe was not use pg_dumpall, but
pg_dump -o -F t livedb > livedb.dump.tar

The use pg_restore

My distribution doesnt have pg_dumpall, but I imagine that unlike pg_dump
where it does

  \connect - someuser

at the top.

It does
\connect origdb someuser

So you probably want to edit a copy of the dump file,
remove the db nmae from the connect statement, then drop the old db
then

You should be able to execute that section of the dump pertaining to the 
db you want to restore on the replacement original, and the new database.

EG:

Your edited snippet of the dump (Checcking all \connect statements to 
ensure they are eiter removed, or refer to currently connected 
database (-) is in dbdump.sql

psql -U username
 > CREATE DATABASE live;
 > \connect live
 > \i dbdump.sql
 > CREATE DATABASE replica;
 > \connect replica
 > \i dbdump.sql
 > \q



> Notice that I have two different databases stored in this file.
> This is what I got:
>
> You are now connected to database template1.
> DELETE 3
> psql:db_10_22_01.bac:7: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:8: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:9: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:11: ERROR:  pg_aclcheck: invalid user id 503
> You are now connected to database template1 as user postgres.
> psql:db_10_22_01.bac:18: ERROR:  CREATE DATABASE: database "webspectest"
>
> already exists
> You are now connected to database webspectest as user postgres.
> CREATE
> DROP
> You are now connected as new user postgres.
> psql:db_10_22_01.bac:48: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'activitytype_pkey' for table 'activitytype'
> psql:db_10_22_01.bac:48: ERROR:  Relation 'activitytype' already exists
> psql:db_10_22_01.bac:65: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'dcr_pkey' for table 'dcr'
>
> Obviously, no database was created. Moreover, I can not access my
> neither of my existing databases anymore.
> When I try:
>> psql webspectest
> I get an error:
> psql: FATAL 1: user "olebedev" does not exist
>
> At this point I am completely stuck.
> Please help.
> thanks,
>
> Oleg
>
>
> ---(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
>
>
--
Best Regards
David Stanaway

Technology Manager
Australia's Premier Internet Broadcasters
Phone: +612 9357 1699
Fax: +612 9357 1169
Web: http://www.netventures.com.au
Support: [EMAIL PROTECTED]

The Inspire Foundation is proudly supported by Net Ventures through the 
provision of streaming solutions for it's national centres.  The Inspire 
Foundation is an Internet-based foundation that inspires young people to 
help themselves, get involved and get online. Please visit Inspire at 
http://www.inspire.org.au


---(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 to sort a birthday list ?

[David Stanaway]

On Thu, 2002-06-20 at 13:55, Michael Agbaglo wrote:
> Hi !
> 
> there's a nice query for retrieving the people who have their birthday 
> in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed., 
> p. 76)
> 
> SELECT *
> FROM Persons
> WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( 
> CURRENT_DATE+60, dateofbirth ) )
> 
> ... but how do I sort the list ?


How about:
ORDER BY dateofbirth





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

[SQL] Unsubscription -- How?

[David Secret]

Can someone please tell me how to unsubscribe from all the pgsql lists?






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

http://archives.postgresql.org

Re: [SQL] graphical interface - admin

[David BOURIAUD]

Le Jeudi 27 Juin 2002 13:50, q u a d r a a écrit :
> What's the best open source GUI for DB administration? (postgres)
Why ? Mine of course !

Noway for this stupid answer !
Well, I would say that you have at least two choices amongst those I know :
phpPgAdmin, which works as it's name says with php, and one that is called 
pgaccess and which is brought along with the sources of postgreSQL, and which 
is written in tcl/tk (as far as I know).

Anyway, neither of them fits my needs. You also have commercial solutions, 
like creating a linked db with msAccess (using ODBC), or BO (orientated for 
editions). Anyway, you've got many choices.

As for what I first said in this mail, I'll launch the development of a Kde 
application (I think called KPostgreSQL...), which will have to fit the 
following :

-Ability to open as many DB connections as possible in one MDI frame (as you 
can open many documents in KAte).
-Graphical interface for all the administrative tasks (tables creations, 
permissions...) nearly as Access does.
-Intelligent queries (at requests), which are able to do autimatic joins (as 
far as they are created at table creation time).
-Nice editions made with LaTeX, providing there are classes made.
-etc...
In the future, when I get a full comprention of KParts, it may be integrated 
with KWords and all the Koffice suite (I mean, you could embed a query in 
KSpread, and so on...).
That's what I plan to do, all in C++, and only for Kde. When the project will 
be far enough, I'll open the sources, using CVS I think, and enjoy my time 
coding and maybe recieving help. Whatever happens in the future, I WANT to 
code this, and will work on it since I need it.
>
>
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021





---(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] SQL problem with aggregate functions.

[David BOURIAUD]

Hi the list !
I've got a table in which there is a field that can have one amongst 3 
possible values : D, R, X. Is it possible to get in one query the count of 
this different values.Please, note that I don't want to have a querry like 
this :
"select count (*) from tab group by f1;", cause i want to get all the possible 
count values in one row (these data are already grouped on another field).
To give a more accurate example, here is what I want to retrieve :

Field group | count of D | count of R | count of X.

Any clues ?
-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021




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

Re: [SQL] is there a way to get hh:mm:ss given seconds

[David Stanaway]

On Mon, 2002-07-08 at 09:28, Narendra A wrote:
> Sir,
> Is there a way in sql such that if I give seconds it should me return me
> hours:mins:seconds
> 
> Eg. Seconds hh:mm:ss
> 422  1:01:02


scratch=# SELECT 422::interval;
 interval 
--
 00:07:02
(1 row)

scratch=# \q
dstanawa@ciderbox:~$ bc -l
7*60+2
422

I don't know where you got 1:01:02 from.


--
David Stanaway



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

Re: [SQL] query/transaction history/logs

[David BOURIAUD]

Le Mercredi 10 Juillet 2002 10:34, q a écrit :
> Is there anyway we can track queries entered?
> Is there a table that stores all the actions that a user entered? (history)
> Is there such a feature?
>
For both of your problems, see the way you can launch the postmaster.
For example, I use to launch it this way :
postmaster -i -d2 &> /var/log/postmaster.log &
Thus, you get all you want in the log file /var/log/postmaster.log
Now, you can get as much infos as you want according to the level of debug you 
want (from 1 to 9 as far as I remember, the greater, the more speech you get 
from the postmaster).
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster

-- 
David BOURIAUD
--
In a world without walls or fences, what use do we have 
for windows or gates ?
--
ICQ#102562021


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Insert Function

[David Durst]

Is there anyway to create a insert function?
I am trying:
CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4)
RETURNS int4 AS 'INSERT INTO usr
(user_name,first_name,last_name,permission_set_id,customer_id) values
($1,$2,$3,$4,$5)' language 'sql';

and get:

ERROR:  function declared to return integer, but final statement is not a
SELECT
I thought that a insert would return a internal row #, but I am not sure
about this.

Thanks,
David Durst
MIS Manager
www.la-rubber.com



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

[SQL] Returning rows from functions

[David Durst]

I was wondering if there was a way of returning a complete row from a
function, in reading the documentation of CREATE FUNCTION. I was under the
impression that you could return a row by using setof, but this does not
seem to be true.
Can anyone help?



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

[SQL] ANNOUNCE: Bricolage 1.3.3

[David Wheeler]

The Bricolage developers are pleased to announce the release of 
Bricolage version 1.3.3!

This the release candidate for Bricolage verion 1.4.0, and is 
considered feature-complete. Nearly 50 new features have been added 
since the 1.2.2 release, and over 80 bugs fixed. Barring any unforseen 
major bugs cropping up, 1.4.0 will be released within a week of this 
release. Please feel give it a try, and report any issues to the 
Bricolage Bugzilla database, at
http://bugzilla.bricolage.cc/.

Learn more about Bricolage and download it from the Bricolage home page,
http://bricolage.cc/.

General description:

Bricolage is a full-featured, enterprise-class content management 
system. It
offers a browser-based interface for ease-of use, a full-fledged 
templating
system with complete programming language support for flexibility, and 
many
other features. It operates in an Apache/mod_perl environment, and uses 
the
PostgreSQL RDBMS for its repository.

Enjoy!

--The Bricolage Team


---(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] Returning a reference to a cursor from a function

[david williams]

Thanks again Richard.   I did find that dev note and I do have a version of this working but of course it does not return up to the ASP layer.   Since I need to integrate Postgresql ( or something else ) into an existing application using COM as the middle and ASP as the upper layer I must create a function similar to how MS SQL Server handles it.   Ah well   Thankgs again for you help and I will keep watch on 7.3. Do you know if the ODBC Driver will also be updated to accomodate this function or is the DECLARE/FETCH setting enough.   Thanks   Dave    - Original Message - From: Richard Huxton Sent: Wednesday, September 18, 2002 6:32 AM To: david williams Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Returning a reference to a cursor from a function  On Tuesday 17 Sep 2002 7:12 pm, you wrote:> Richard,>> Thanks for the information. I've made some modifications to your code here> so that it does a RAISE NOTICE in each loop returning simply the value of n> and then when the loop is finished it again returns n.>> This works fine at the psql level but after it passes through ODBC to the> ASP layer all I get is the final RETURN value.Yep - the NOTICE is really a type of error message (you can use RAISE to generate errors too) and isn't part of your data-stream.> I have tried using the RETURN function in the loop but it terminates the> loop.Indeed it does.> I really need to return each record up to the ASP layer.The solution to this sort of thing in version 7.3 is something called table functions, but I think they're limited to C at the moment, not plpgsql.With 7.2 you need to return the cursor from the function and then FETCH from it. An example was missed out from the 7.2.1 docs but you can see one in the developer's docs (bottom of page):http://developer.postgresql.org/docs/postgres/plpgsql-cursors.htmlTo hack our example a bit more the below takes a cursor-name and table name and defines a cursor for you.Note that when using it, you need to be within BEGIN...COMMIT (an explicit transaction) since the cursor returned from the function only lasts until the end of a transaction.HTH- Richard HuxtonDROP FUNCTION foo_count(refcursor, text);CREATE FUNCTION foo_count(refcursor, text) RETURNS refcursor AS 'DECLARE    curs ALIAS FOR $1;    tbl_name ALIAS FOR $2;BEGIN    RAISE NOTICE ''cursor on table: %'',tbl_name;    OPEN curs FOR EXECUTE ''SELECT * FROM '' || tbl_name;    RETURN curs;END;'language 'plpgsql';richardh=> BEGIN;BEGINrichardh=> SELECT foo_count('fake_cursor','companies');NOTICE:  cursor on table: companies  foo_count-fake_cursor(1 row)richardh=> FETCH 3 FROM fake_cursor;co_id | co_name  | co_postcode |  co_lastchg---+--+-+---    56 | Acme Associates GmBH | unknown | 2002-06-12 14:04:43.123408+01    57 | Imperial Investments Inc | unknown | 2002-06-12 14:04:43.123408+01    58 | Universal Associates USA | unknown | 2002-06-12 14:04:43.123408+01(3 rows)richardh=> COMMIT;---(end of broadcast)---TIP 2: you can get off all lists at once with the unregister command    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

[SQL] Is there a better way than this to get the start and end of a month?

[David Stanaway]

Here are the 2 functions I have at the moment. I was wondering if
someone had a better way?

CREATE OR REPLACE FUNCTION month_start (date)
  RETURNS date
  AS '
   DECLARE
day ALIAS FOR $1;
   BEGIN
RETURN day - (extract(''day'' FROM day)||'' days'')::interval + 
''1 day''::interval;  
   END;
  '
  LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION month_end (date)
  RETURNS date
  AS '
   DECLARE
day ALIAS FOR $1;
month int;
year int;
   BEGIN
month := extract(''month'' FROM day);
year  := extract(''year'' FROM day);
IF month = 12 THEN
 month := 1;
 year  := year +1;
ELSE
 month := month +1;
END IF;
    RETURN (''01-''||month||''-''||year)::date - 
''1 day''::interval;
END;
   '
   LANGUAGE 'plpgsql';

-- 
David Stanaway

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

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

Re: [SQL] Stored Procedures

[david williams]

Stored procedures returning more than one row up through odbc does not work in 7.2.1   To return more than one column you must spec is column in the returns area of the function.   Dave    - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, October 02, 2002 1:53 PM To: Joe Conway Cc: david williams; [EMAIL PROTECTED] Subject: Re: [SQL] Stored Procedures  Ok, if this does not apply to versions prior to 7.3beta then what do I need to do if I am running 7.2.1? When I try to use the SETOF to retrun a row set, I only get one column.Do I need to update Postgres to get things to work?Ben> david williams wrote:> > Also,> >  > > the table definition MUST be in the Public Schema. I use my own schema > > names but in order for the table to be found by the function it ( the > > table ) must be in the public schema. Although it can be empty.> > (Note:>   this discussion does not apply to PostgreSQL releases prior to 7.3 beta)> > Not true. You need to be sure the schema the table is in is in your search > path, or you need to fully qualify the table reference. See below for an > example:> > -- create a new schema> CREATE SCHEMA s1;> CREATE SCHEMA> -- change to the new schema> SET search_path='s1','$user','public';> SET> select current_schema();>   current_schema> >   s1> (1 row)> > -- create the table> CREATE TABLE foo (fooid int, foosubid int, fooname text);> CREATE TABLE> INSERT INTO foo VALUES(1,1,'Joe');> INSERT 794076 1> -- change back to public schema, but leave s1 in the search path> SET search_path='$user','public','s1';> SET> select current_schema();>   current_schema> >   public> (1 row)> > \dt>  List of relations>   Schema | Name | Type  |  Owner> +--+---+-->   s1 | foo  | table | postgres> (1 row)> > CREATE FUNCTION getfoo(int) RETURNS foo AS '>    SELECT * FROM foo WHERE fooid = $1;> ' LANGUAGE SQL;> CREATE FUNCTION> \df getfoo>  List of functions>   Result data type | Schema |  Name  | Argument data types> --+++->   foo  | public | getfoo | integer> (1 row)> > -- this will work> SELECT *, upper(fooname) FROM getfoo(1) AS t1;>   fooid | foosubid | fooname | upper> ---+--+-+--->   1 |    1 | Joe | JOE> (1 row)> > -- now try again with table name qualified in the function> DROP FUNCTION getfoo(int);> DROP FUNCTION> -- remove s1 from the search path> SET search_path='$user','public';> SET> select current_schema();>   current_schema> >   public> (1 row)> > \dt> No relations found.> CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '>    SELECT * FROM s1.foo WHERE fooid = $1;> ' LANGUAGE SQL;> CREATE FUNCTION> \df getfoo>  List of functions>   Result data type | Schema |  Name  | Argument data types> --+++->   s1.foo   | public | getfoo | integer> (1 row)> > -- this will work> SELECT *, upper(fooname) FROM getfoo(1) AS t1;>   fooid | foosubid | fooname | upper> ---+--+-+--->   1 |    1 | Joe | JOE> (1 row)> > HTH,> > Joe> Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

Re: [SQL] Stored Procedures

[david williams]

http://developer.postgresql.org/docs/postgres/xfunc-sql.html#AEN30400   See section    9.2.4. SQL Table Functions    - Original Message - From: [EMAIL PROTECTED] Sent: Tuesday, October 01, 2002 4:25 PM To: [EMAIL PROTECTED] Subject: [SQL] Stored Procedures  Hi all. I'm looking for a little help here. I have a project where I have to write some stored proceedures and am having some problems. My main issue is, I cannot figure out how to return a record set containing multipule columns. I am looking for a few examples on how I can do this. Most of what I have to do is fairly simple SQL queries based on a pramater sent to the function. I tried to use the SETOF  option, but only get back one column.Any help will be would be greatly appricated. Simple examples would be of a great help.Thanks,Ben---(end of broadcast)---TIP 6: Have you searched our list archives?http://archives.postgresql.orgGet more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

Re: [SQL] Stored Procedures

[david williams]

Also,   the table definition MUST be in the Public Schema. I use my own schema names but in order for the table to be found by the function it ( the table ) must be in the public schema. Although it can be empty.   DaveGet more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

[SQL] trigger to maintain relationships

[David M]

I am maintaining a set of hierarchical data that looks a lot like a
tree.  (And my SQL is very rusty.  And I'm new to postgres.)

Questions:
-
1.)  Is the following a reasonable solution?  Is there a
postgres-specific way to handle this better?  Is there a good generic
SQL way to handle this?
2.)  Can I write pure "SQL" triggers to handle this?  Am I getting close
in my first cut (below)?
3.)  Any other ideas/suggestions?


I have one table with essentially the nodes of a tree:

nodes
--
node_id integer
parent_id   integer references nodes(node_id)
...and other descriptive columns...

I want an easy way to find all the elements of a subtree.  Not being
able to think of a good declarative solution, I was thinking about
cheating and maintaining an ancestors table:

ancestors
---
node_idinteger
ancestor_id   integer references nodes(node_id)

I figured I could populate the ancestors table via trigger(s) on the
nodes table.  Then I should be able to find a whole subtree of node X
with something like:

select *
from nodes
where node_id in (
select node_id
from ancestors
where ancestor_id = X)

Here's my best guess so far at the triggers (but, obviously, no luck so
far):

--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();

--delete trigger
create function pr_tr_d_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;'
language sql;
create trigger tr_d_nodes after insert
on nodes for each row
execute procedure pr_tr_d_nodes();

--update trigger
create function pr_tr_u_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;

insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_u_nodes after insert
on nodes for each row
execute procedure pr_tr_u_nodes();

I realize the update trigger could be handled a multitude of ways and
that my first guess may be pretty lousy.  But I figured the
insert/update triggers would be pretty straightforward.  Am I missing
something basic?  I also tried things like (following the one example in
the reference manual):

--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;

return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();



---(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] trigger to maintain relationships

[David M]

I think I figured out my join syntax error (sorry for confusing the issue
with noise like that).  I'd still be interested in general comments on
design.

FYI, join should've looked like:

create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from NEW left outer join ancestors on (NEW.parent_id =
ancestors.node_id);

return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();




David M wrote:

> I am maintaining a set of hierarchical data that looks a lot like a
> tree.  (And my SQL is very rusty.  And I'm new to postgres.)
>
> Questions:
> -
> 1.)  Is the following a reasonable solution?  Is there a
> postgres-specific way to handle this better?  Is there a good generic
> SQL way to handle this?
> 2.)  Can I write pure "SQL" triggers to handle this?  Am I getting close
> in my first cut (below)?
> 3.)  Any other ideas/suggestions?
>
> I have one table with essentially the nodes of a tree:
>
> nodes
> --
> node_id integer
> parent_id   integer references nodes(node_id)
> ...and other descriptive columns...
>
> I want an easy way to find all the elements of a subtree.  Not being
> able to think of a good declarative solution, I was thinking about
> cheating and maintaining an ancestors table:
>
> ancestors
> ---
> node_idinteger
> ancestor_id   integer references nodes(node_id)
>
> I figured I could populate the ancestors table via trigger(s) on the
> nodes table.  Then I should be able to find a whole subtree of node X
> with something like:
>
> select *
> from nodes
> where node_id in (
> select node_id
> from ancestors
> where ancestor_id = X)
>
> Here's my best guess so far at the triggers (but, obviously, no luck so
> far):
>
> --insert trigger
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;'
> language sql;
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();
>
> --delete trigger
> create function pr_tr_d_nodes() returns opaque
> as '
> delete from ancestors
> where node_id = OLD.parent_id;'
> language sql;
> create trigger tr_d_nodes after insert
> on nodes for each row
> execute procedure pr_tr_d_nodes();
>
> --update trigger
> create function pr_tr_u_nodes() returns opaque
> as '
> delete from ancestors
> where node_id = OLD.parent_id;
>
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;'
> language sql;
> create trigger tr_u_nodes after insert
> on nodes for each row
> execute procedure pr_tr_u_nodes();
>
> I realize the update trigger could be handled a multitude of ways and
> that my first guess may be pretty lousy.  But I figured the
> insert/update triggers would be pretty straightforward.  Am I missing
> something basic?  I also tried things like (following the one example in
> the reference manual):
>
> --insert trigger
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;
>
> return NEW;'
> language 'plpgsql';
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();
>
> ---(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


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

[SQL] unsubscribe

[david williams]

unsubscribe

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


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

[SQL] Returning row or rows from function?

[David Durst]

I want to create a function that will return
a row or rows of a table is this possible?

If so can someone replay with a complete example?



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

[SQL] returning setof in plpgsql

[David Durst]

I have a function that I want to return setof a table in plpgsql.

Here is what I have:

CREATE FUNCTION lookup_account(varchar(32)) RETURNS SETOF accounts AS '
DECLARE
  aname ALIAS FOR $1;
  rec RECORD;
BEGIN
  select into rec * from accounts where accountname = aname;
  return rec;
END;'
LANGUAGE 'plpgsql';

This seems to hang when I attempt to select it using:

select accountid(
lookup_account('some account')),
accountname(lookup_account('some account')),
type(lookup_account('some account')),
balance(lookup_account('some account'));

Does anyone see a problem w/ my approach??




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

[SQL] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index

[David Durst]

Can anyone tell me why postgres is creating a implicit index when
I already have a PKEY specified

Or am I just interpreting this all wrong?

Here is the entry I am putting in:

create sequence journal_line_id_seq increment 1 start 1;

create table journal_lines (
  journal_line_id int4 PRIMARY KEY DEFAULT NEXTVAL('journal_line_id_seq'),
  entry_id int4,
  account_id int4,
  line_type int2 CHECK (line_type >= 1 AND line_type <= 2),
  line_amount money,
  CONSTRAINT eid FOREIGN KEY(entry_id) REFERENCES journal(entry_id),
  CONSTRAINT aid FOREIGN KEY(account_id) REFERENCES accounts(account_id)
);

Here is the notice postgres spits out:
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'journal_lines_pkey' for table 'journal_lines'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE




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

http://archives.postgresql.org

[SQL] Scheduling Events?

[David Durst]

Is there anyway to schedule DB Events based on time?
So lets say I had a table w/ depreciation schedules in it,
I would like the DB to apply the formula and make the entries on the END
of every month.



---(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] Scheduling Events?

[David Durst]

On a side note, if the DB doesn't support this capability.
Does anyone see a HORRIBLE issue w/ creating a C func
something of this nature.


int handle_temporal_events() {
  if(fork == 0) {
//In here we monitor what time it is
//And maintain a Datastructure w/ events
//And update it every so often
//Then preform various background tasks
  }
  else if(fork == -1) {
//Thread error
  }
}



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

http://archives.postgresql.org

Re: [SQL] Scheduling Events?

[David Durst]

> On Thu, 23 Jan 2003, David Durst wrote:
>
>> Is there anyway to schedule DB Events based on time?
>
> Yes! cron
>
>> So lets say I had a table w/ depreciation schedules in it,
>> I would like the DB to apply the formula and make the entries on the
>> END of every month.
> On Thu, 23 Jan 2003, David Durst wrote:
>
>> Is there anyway to schedule DB Events based on time?
>
> Yes! cron
>
>> So lets say I had a table w/ depreciation schedules in it,
>> I would like the DB to apply the formula and make the entries on the
>> END of every month.

Here is the basic problem w/ using CRON in an accounting situation.

I can't be sure that cron will always be up when the DB is up,
so lets say crond goes down for some random reason (User, System error,
Etc..)

And outside adjustment is made to lets say the equipment account and that
adjustment was made on the value of the equipment, BUT it hadn't been
depreciated because crond went down and no one notice.

Now I have a HUGE issue!

So I have to be sure that all entries/adjustments are made accurately in
the time frame they were meant to happen in.



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

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

Re: [SQL] Scheduling Events?

[David Durst]

here is a possible NON-Cron solution that a friend of mine came up w/

1) Create a table w/ scheduled events and Account Ids attached to them.
2) Create a table w/ temporal event execution timestamps.
3) On journal entry check to see if there any schedule events for the Account
4) Check timestamp table for last execution
   If Last execution is out of range
 force execution
   Else
 continue as normal

This is passive but it should allow for data integrity w/ out the need of
a external system.






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

[SQL] Function for adding Money type

[David Durst]

Are there functions for adding and subtracting this type from itself?
Or is there a simple way to do it?



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

Re: [SQL] Function for adding Money type

[David Durst]

> David,
>
>> Are there functions for adding and subtracting this type from itself?
>> Or is there a simple way to do it?
>
> The MONEY type is depreciated, and should have been removed from the
> Postgres  source but was missed as an oversight.   Use NUMERIC instead.
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
Already done, I found it in another doc.
Thanks though



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

[SQL] ERROR: Cannot display a value of type RECORD

[David Durst]

I recieve this error when executing the following function:

select lookup_journal_entries(to_date('20030125','MMDD'),
  to_date('20030125','MMDD'));

Here is the function itself:

create function lookup_journal_entries(date,date) returns setof journal as '
select * from journal where entry_date >= $1 OR entry_date <= $2'
language 'SQL';

Normally I would expect to see a pointer # returned from the above select
but instead I get this error.



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

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

Re: [SQL] ERROR: Cannot display a value of type RECORD

[David Durst]

I figured out the issue, DH stupid mistake

select entry_id, entry_description from
lookup_journal_entries(to_date('20030125','MMDD'),
   to_date('20030125','MMDD'));

> I recieve this error when executing the following function:
>
> select lookup_journal_entries(to_date('20030125','MMDD'),
>   to_date('20030125','MMDD'));
>
> Here is the function itself:
>
> create function lookup_journal_entries(date,date) returns setof journal
> as ' select * from journal where entry_date >= $1 OR entry_date <= $2'
> language 'SQL';
>
> Normally I would expect to see a pointer # returned from the above
> select but instead I get this error.
>
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html




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

http://archives.postgresql.org

[SQL] LONG - Question on dealing w/ numerics

[David Durst]

I have a function that is to create a Accounting JOURNAL entry.
The strange thing is the function works for simple entries such as:

Cash - Debit  100
A/R  - Credit 100

But when I try to trick it or break it for testing purposes (IT DOES BREAK
WHEN IT SHOULDN'T) on a entry like this:

Cash - Debit  100
A/R  - Credit 100
Cash - Credit 100
A/R  - Debit  100
(Which should have a net affect of 0 on both accounts)

But here is the resulting balance on accounts,

Cash Debit  Balance 200
A/R  Credit Balance 200

Here is the function and I can't seem to figure out what is LOGICALLY
wrong and would produce these results.

create function
create_journal_entry_line(integer,integer,integer,numeric(20,2)) returns
INTEGER as '
DECLARE
  eid ALIAS FOR $1;
  aid ALIAS FOR $2;
  ltype ALIAS FOR $3;
  amount ALIAS FOR $4;
  new_balance  NUMERIC(20,2);
  account_type RECORD;
  account  RECORD;
  line RECORD;
BEGIN
  select into account * from accounts where account_id = aid;

  IF NOT FOUND THEN
return -1;
  END IF;

  IF account.account_active = ''f'' THEN
return -1;
  END IF;

  insert into journal_lines (entry_id,account_id,line_type,line_amount)
values (eid,aid,ltype,amount);
  select into line * from journal_lines where entry_id = eid AND
account_id = aid AND ltype = ltype;
  IF NOT FOUND THEN
return -1;
  END IF;

  select into account_type * from account_types where account_type_id =
account.account_type;

  IF account_type.positive_account_balance_type = line.line_type THEN
new_balance := account.account_balance + amount;
  ELSE
new_balance := account.account_balance - amount;
  END IF;
  UPDATE accounts SET account_balance = new_balance WHERE account_id =
account.account_id;
  return line.entry_id;
END;' language 'plpgsql';

P.S. Line type represents 1 = Debit, 2 = Credit.  The
positive_account_balance_type tells eithier if the account should have a
DEBIT or CREDIT balance (Represented the same as line type)



---(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] LONG - Question on dealing w/ numerics

[David Durst]

> "David Durst" <[EMAIL PROTECTED]> writes:
>>   insert into journal_lines
>> (entry_id,account_id,line_type,line_amount)
>> values (eid,aid,ltype,amount);
>>   select into line * from journal_lines where entry_id = eid AND
>> account_id = aid AND ltype = ltype;
>
> I bet that last should be line_type = ltype?
Just to let you know, changing ltype to line_type fixed the problem.

But I still think your point about the function selecting more than
one line is valid.

The problem is, the journal_line_id is not created until the insert
occurs and there is no other unique ident than the journal_line_id.



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

[SQL] Question about passing User defined types to functions

[David Durst]

is there a example on how to pass user defined types into
a function??

What I am looking for is something of this nature.

CREATE TYPE dumby_type AS (dumby_id int4, dumby_name text);

create function kick_dumby(dumby dumby_type) returns INTEGER AS '
DECLARE
  somenumber integer;
BEGIN
  return 1;
END;
' language 'plpgsql';


Is there some way of doing this, because the above doesn't work.



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

[SQL] Postgres MD5 Function

[David Durst]

Does there exsist a MD5 Function I can call???

If not, is there any interest in one?



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

http://archives.postgresql.org

Re: [SQL] Postgres MD5 Function

[David Durst]

Is there anywhere I can get these in binary?
Or is my only option to compile Postgres from source??
> Larry Rosenman wrote:
>> --On Friday, January 31, 2003 01:34:42 -0800 David Durst
>> <[EMAIL PROTECTED]> wrote:
>>> Does there exsist a MD5 Function I can call???
>>
>> look at /contrib/pgcrypto in the source distribution.
>>
>
> Also worth noting is that 7.4 will have (and cvs HEAD has) a builtin md5
> function:
>
> regression=# select md5('Joe');
> md5
> --
>   3a368818b7341d48660e8dd6c5a77dbe
> (1 row)
>
> HTH,
>
> Joe
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Commenting PLPGSQL

[David Durst]

Is it possible to have comment lines inside PLPGSQL??



---(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] The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard

[David Delorme]

I am trying to integrate MapInfo with a postgresql backend.
Problem:
MapInfo query uses both quoted and unquoted statements i.e.
"MAPINFO"."MAPINFO_MAPCATALOG" and mapinfo.mapinfo_mapcatalog
I can not change the query statements that MapInfo uses to talk to the odbc
driver.
I am in the process of changing to grass5 for all my map production.

I have a schema called MAPINFO and a table called MAPINFO_MAPCATALOG.
I need to fold unquoted names to uppercase.

I really only need to change this behaviour for this schema.

David Delorme
http://dmdelorme.ca


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

http://archives.postgresql.org

[SQL] Casting with character and character varying

[David Loh]

Hi all.
Recently I face some problem with casting character type variable and
varchar variable.
The situation was like: I had 2 table, on table A, the user_name is defined
as character(32), and table B uses varchar(32). I have 1 function and a
trigger to manipulate with these data.

Here's the function: (NEW = tableB)
--
create or replace function prepaid () returns trigger as '
  declare Rec tableA%ROWTYPE;

begin
  if NEW.status != 2 then
   return NEW;
  else
  select into Rec * from tableA where user_name = trim(trailing '' '' from
cast(NEW.user_name as varchar)) and user_type = ''T'';
   if not found then
return NEW;
   end if;

   insert into temptable values (tableA.FieldA);
  end if;
  return NEW;
end;
' language 'plpgsql';
-
supposingly the insert will insert the value of field A in table into
temptable (declare as varchar(100)), instead of inserting single row, the
insert actually insert all data from tableA to temptable (if there's 10 row
in tableA, the insert statement will insert all to temptable), that's weird.

Then i tried with cast(trim(trailing '' '' from NEW.user_name)::varchar as
text), and it's returns me with nothing (suppose there'll be 1 record
matched).

If any of you guys willing to help me out, I'll apprepriate it. Or you may
point me to some postgresql casting tutorial.

Thanks.


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

[SQL] Help with LIKE

[David Olbersen]

I have a table with 8,628,633 rows that I'd LIKE to search (ha ha).

I have a very simple query:
  SELECT * FROM tableA WHERE column1 LIKE '%something%';

tableA.column1 has an index on it and the database has been vacuumed recently. My 
problem is with the output of EXPLAIN:

++
| QUERY PLAN |
++
| Seq Scan on tableA  (cost=0.00..212651.61 rows=13802 width=46) |
|   Filter: (column1 ~~ '%something%'::text) |
++

I don't like that cost (2,12,651) at all! Is there anyway I can optimize this query? 
Make a different kind of index (it's currently btree)? Use substr or indexof or 
something instead of LIKE?

Thoughts?

--
David Olbersen 
iGuard Engineer
11415 West Bernardo Court 
San Diego, CA 92127 
1-858-676-2277 x2152

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] What this parser mean?

[David Witham]

Hi Abdul,

You may have lost the '' around the date specification in the to_char function when 
forming the string in Java. Before submitting the query, check the contents of the 
query string to make sure you still have the '' marks.

Regards,
David Witham

-Original Message-
From: Abdul Wahab Dahalan [mailto:[EMAIL PROTECTED]
Sent: Friday, 21 March 2003 13:47
To: [EMAIL PROTECTED]
Subject: [SQL] What this parser mean?


I wrote a java program to query a data from 4 tables and the string
query is like:

querydata ="select
bizfavorite.bizid,bizprofile.bizname,bizprofile.bizphone,bizprofile.bizfax,bizfavorite.searchtype,"

 + "bizfavorite.keyword,to_char(bizfavorite.listdate,'DD-MM-') as
listdate,bizfavorite.offerid,offer.otype,offer.bizid as obizid,"
 + "to_char(offer.oposted_date,'DD-MM-') as
oposted_date,to_char(offer.oexpiry_date,'DD-MM-') as oexpiry_date,"
 + "userprofile.username,userprofile.ufullname,userprofile.uemail"
 + " from bizfavorite join bizprofile using(bizid) join userprofile
using(bizid) left join offer using(offerid)"
 + " where bizfavorite.username= ? and urole='1' order by listdate desc"

 + " limit " + recordPerpage + "," + beginRecord;

When I run this query, I got this message : Message: ERROR:  parser:
parse error at or near "-"

but when I ran this query from psql command prompt its ok.I got what I
want.
b2bscm=> select
bizprofile.bizname,bizprofile.bizphone,bizprofile.bizfax,bizfavorite.searchtype,bizfavorite.keyword,to_char(bizfavorite.listdate,'DD-MM-')
as listdate,bizfavorite.offerid,offer.otype,offer.bizid as
obizid,to_char(offer.oposted_date,'DD-MM-') as
date,to_char(offer.oexpiry_date,'DD-MM-') as
oexpiry_date,userprofile.username,userprofile.ufullname,userprofile.uemail
from bizfavorite join bizprofile using(bizid) join userprofile
using(bizid) left join offer using(offerid) where bizfavorite.username=
'faiz' and urole='1' order by listdate desc limit 8,0;

Any help, very much appreciated..Thanks


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

---(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] FUNCTIONS PROBLEM

[David Witham]

Hi Mario,

I have used a record type to do this:

create myfunc() returns record as '

declare
return_val  record;
col1int;
col2int;
col3real;
col4char;

col1 := 5;
col2 := 10;
col3 := 2.7;
col4 := ''z'';

select col1,col2,col3,col4 into return_val;
return return_val;
end;
' language 'plpgsql';

When you call the function you need to specify the expected output:

select * from myfunc() as (val1 int, val2 int, val3 real, val4 char);

See the SELECT reference page in the documentation.

There are other ways (which may be better) to do this that don't require the output 
types to be specified with the query but this is the one I got going first so I stuck 
with it. Hope this helps.

Regards,
David Witham
Telephony Platforms Architect
Unidial

-Original Message-
From: Mario Alberto Soto Cordones [mailto:[EMAIL PROTECTED]
Sent: Friday, 21 March 2003 09:26
To: [EMAIL PROTECTED]
Subject: [SQL] FUNCTIONS PROBLEM
Importance: High


Hi.

i have a function and i need to return 4 fields but not work,

any idea , please

thank

mario



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

[SQL] Rows UPDATEd?

[David Olbersen]

I'm looking for a way to see how many rows were UPDATEd. I Googled a bit and found 
that if I were using pl/pgsql I could use 'GET DIAGNOSTICS'. But I'm not using 
pl/pgsql. Is there some other way I could find out how many rows were affected by the 
last statement in my transaction?

For what it's worth, a Perl script is doing this using the Pg module. I didn't see 
anything in the Pg man page describing this. It does cover INSERT and DELETE by using 
"$cmdStatus = $result->cmdStatus", but not UPDATE.

Any suggestions?

--
David Olbersen 
iGuard Engineer
11415 West Bernardo Court 
San Diego, CA 92127 
1-858-676-2277 x2152

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

http://archives.postgresql.org

Re: [SQL] Rows UPDATEd? (solved!)

[David Olbersen]

Using the Pg module, one can use

$res->cmdStatus for UPDATE as well as INSERT and DELETE to see how many rows were 
UPDATE/INSERT/DELETEd and what action was taken.

One can also use $res->cmdTuples in the same manner.

Looks like the documentation is just a little lacking.

--
David Olbersen 
iGuard Engineer
11415 West Bernardo Court 
San Diego, CA 92127 
1-858-676-2277 x2152


> -Original Message-
> From: David Olbersen 
> Sent: Thursday, May 29, 2003 10:01 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Rows UPDATEd?
> 
> 
> 
> I'm looking for a way to see how many rows were UPDATEd. I 
> Googled a bit and found that if I were using pl/pgsql I could 
> use 'GET DIAGNOSTICS'. But I'm not using pl/pgsql. Is there 
> some other way I could find out how many rows were affected 
> by the last statement in my transaction?
> 
> For what it's worth, a Perl script is doing this using the Pg 
> module. I didn't see anything in the Pg man page describing 
> this. It does cover INSERT and DELETE by using "$cmdStatus = 
> $result->cmdStatus", but not UPDATE.
> 
> Any suggestions?
> 
> --
> David Olbersen 
> iGuard Engineer
> 11415 West Bernardo Court 
> San Diego, CA 92127 
> 1-858-676-2277 x2152
> 
> ---(end of 
> broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

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

[SQL] SELECT statement within libpq

[David Klugmann]

Hi

I'm knew to postgres and I got my first C postgres connection working from 
the examples in the documentation.

In that it does a BEGIN and a DECLARE CURSOR FOR SELECT et.c. This seems a 
long way to go about getting back data each time.

Is it not possible to just do a straight select and not a transaction and a 
cursor ?

Also does anyone have any generic routines for handling results of all types 
of select.

Many thanks

David

_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail

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

[SQL] (long) What's the problem?

[David Olbersen]

Anybody have any ideas about a problem with this query?

urldb2=> EXPLAIN
urldb2-> SELECT
urldb2->   id,
urldb2->   source,
urldb2->   insertedby,
urldb2->   insertedon,
urldb2->   priority
urldb2-> FROM
urldb2->   indexscan
urldb2-> WHERE
urldb2->   lower(
urldb2-> substring(
urldb2->   urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2->   ) ~ '^q.*'
urldb2-> ORDER BY source;  
  

QUERY PLAN 
---
 Sort  (cost=23.50..23.50 rows=3 width=48)
   Sort Key: source
   ->  Seq Scan on indexscan  (cost=0.00..23.47 rows=3 width=48)
 Filter: (lower("substring"(urlhost(source), (rposition('www.'::text, 
(urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)

OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:

urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2->   id,
urldb2->   source,
urldb2->   insertedby,
urldb2->   insertedon,
urldb2->   priority
urldb2-> FROM
urldb2->   indexscan
urldb2-> WHERE
urldb2->   lower(
urldb2-> substring(
urldb2->   urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2->   ) ~ '^q.*'
urldb2-> ORDER BY source;

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included 
that in this email because the error is the same.

urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are 
trying to use massive amounts of resources. This is a P3 550 with 512MB of RAM.

I can provide more information if needed.

Anybody have ideas about the problem?

--
David Olbersen 
iGuard Engineer
11415 West Bernardo Court 
San Diego, CA 92127 
1-858-676-2277 x2152

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

[SQL] (long) What's the problem?

[David Olbersen]

Anybody have any ideas about a problem with this query?

urldb2=> EXPLAIN
urldb2-> SELECT
urldb2->   id,
urldb2->   source,
urldb2->   insertedby,
urldb2->   insertedon,
urldb2->   priority
urldb2-> FROM
urldb2->   indexscan
urldb2-> WHERE
urldb2->   lower(
urldb2-> substring(
urldb2->   urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2->   ) ~ '^q.*'
urldb2-> ORDER BY source;  
  

QUERY PLAN 
---
 Sort  (cost=23.50..23.50 rows=3 width=48)
   Sort Key: source
   ->  Seq Scan on indexscan  (cost=0.00..23.47 rows=3 width=48)
 Filter: (lower("substring"(urlhost(source), (rposition('www.'::text, 
(urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)

OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:

urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2->   id,
urldb2->   source,
urldb2->   insertedby,
urldb2->   insertedon,
urldb2->   priority
urldb2-> FROM
urldb2->   indexscan
urldb2-> WHERE
urldb2->   lower(
urldb2-> substring(
urldb2->   urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2->   ) ~ '^q.*'
urldb2-> ORDER BY source;

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included 
that in this email because the error is the same.

urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are 
trying to use massive amounts of resources. This is a P3 550 with 512MB of RAM.

I can provide more information if needed.

Anybody have ideas about the problem?

--
David Olbersen 
iGuard Engineer
11415 West Bernardo Court 
San Diego, CA 92127 
1-858-676-2277 x2152

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]