Re: [SQL] Problems invoking psql. Help please.
> Here are the results from reversing the arguments. > > >hesco@biko:~$ su postgres > >Password: > >postgres@biko:/home/hesco$ cd > >postgres@biko:~$ cd /usr/bin > >postgres@biko:/usr/bin$ psql tempate1 -U postgres > >Could not execv /usr/lib/postgresql/bin/psql > >postgres@biko:/usr/bin$ psql template1 -U postgres > >Could not execv /usr/lib/postgresql/bin/psql > >postgres@biko:/usr/bin$ Check the permissions. Psql is only a symbolic link to pg_wrapper. You should have: ls -al /usr/bin/pg_wrapper -rwxr-xr-x1 root root 6584 sie 25 23:55 /usr/bin/pg_wrapper > If I compile from source, will the apt-get database know what I've > done? Or will I have to do the updates from source as well? No. If you want to create package .deb from your sources, look at debian packages source site. There is special debian patch in postgres directory. Apply it, compile your sources and create .deb package. The last step is to install this package with dpkg. Regards, Tomasz Myrta ---(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] Problems invoking psql. Help please.
biko:/usr/bin# psql -U postgres No database specified Instead, do: su - postgres Then do: psql template1 or psql -h template1 My pg_hba.conf temporarily reads: local all trust host all 127.0.0.1 255.255.255.255 trust host template1 192.168.2.21 255.255.255.0 trust For now, just remove any security and put this line: host all 192.168.2.21 255.255.255.255 trust smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] trying to learn plpqsql... so please forgive..
When I do an 'INSERT INTO VALUES ' and on the table is a serial primary key named p_key. As I want this number to be auto-generated, but use it as a 'customer number', I want to create this function to return the value of this insert. Try this: SELECT currval(); Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
[SQL] Date trunc in UTC
Hi
I do not know if it's an error, but in this query
=# select date_trunc ('month', now ());
date_trunc
2002-11-01 00:00:00+01
(1 row)
I've got the truncated date dependant to my timezone.
Instead, I would like to have as a result
2002-11-01 01:00:00+01
which is correct, but I cannot set the whole server to UTC. Any way to
get this ?
Thanks
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] trying to learn plpqsql... so please forgive..
Title: RE: [SQL] trying to learn plpqsql... so please forgive..
Michiel Lange wrote:
> Maybe this should be sent to novice... I was not certain, but if it
> should, please tell me so.
>
> The matter at hand is this:
>
> When I do an 'INSERT INTO VALUES '
> and on the table is a serial primary key named p_key.
> As I want this number to be auto-generated, but use it as a 'customer
> number', I want to create this function to return the value of this
> insert. I thought/hoped that this would work, but as there are some
> people dependant on this database, I dare not try out too much ;-)
> This would be the first time I used plpgsql, so I am not so certain
> about what I do.
>
> CREATE FUNCTION add_cust() RETURNS INT4 AS ' -- SERIAL data type is
> really an INT4 (and some more).
> BEGIN
> RETURN NEW.p_key;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER add_cust BEFORE INSERT ON table
> FOR EACH ROW EXECUTE PROCEDURE add_cust();
>
>
> Someone willing to evaluate this for me, and telling me if it is safe
> to use as it is? or things I may do wrong?
>
> TIA,
> Michiel
>
>
Trigger functions can only return type OPAQUE which isn't seen by the client program.
To get the value of the serial field for the last insert do:
SELECT currval('TableName_SerialFieldName_seq');
This will get the last value from the sequence used by this connection (although it will error if no values have been requested).
hth,
- Stuart
Re: [SQL] trying to learn plpqsql... so please forgive..
On Wednesday 20 November 2002 10:48, Henshall, Stuart - Design & Print wrote: > Michiel Lange wrote: > Trigger functions can only return type OPAQUE which isn't seen by the > client program. I just saw that in 7.3 the return type for triggers has changed to TRIGGER. HTH Johannes Lochmann ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Date trunc in UTC
On Wednesday 20 Nov 2002 9:44 am, Thrasher wrote:
> Hi
>
> I do not know if it's an error, but in this query
>
> =# select date_trunc ('month', now ());
> date_trunc
>
> 2002-11-01 00:00:00+01
> (1 row)
>
> I've got the truncated date dependant to my timezone.
>
> Instead, I would like to have as a result
>
> 2002-11-01 01:00:00+01
>
> which is correct, but I cannot set the whole server to UTC. Any way to
> get this ?
Perhaps SET TIME ZONE is what you want. See the manual section on date/time
types for details.
--
Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Date trunc in UTC
No I cannot use SET TIME ZONE.
SET TIME ZONE will be set by any client backend. But what I want to get
is that DATE_TRUNC('month', ) = DATE_TRUNC('month',
).
Richard Huxton wrote:
On Wednesday 20 Nov 2002 9:44 am, Thrasher wrote:
Hi
I do not know if it's an error, but in this query
=# select date_trunc ('month', now ());
date_trunc
2002-11-01 00:00:00+01
(1 row)
I've got the truncated date dependant to my timezone.
Instead, I would like to have as a result
2002-11-01 01:00:00+01
which is correct, but I cannot set the whole server to UTC. Any way to
get this ?
Perhaps SET TIME ZONE is what you want. See the manual section on date/time
types for details.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 06:30, Hugh Esco wrote: > I did this tonight > > dpkg --purge postgresql > apt-get install postgresql > > and am now still getting the following: > > >biko:/usr/bin# psql -U postgres > >No database specified > >biko:/usr/bin# psql -U postgres template1 > >Could not execv /usr/lib/postgresql/bin/psql There is something wrong with permissions here. You ought to be able, as _any_ user, to run /usr/lib/postgresql/bin/psql Can you? It seems clear that pg_wrapper can't. If not, why not? I assume the file must exist, since you have just reinstalled the package. Is the file itself executable by all users? (Use "ls -l" to check this.) Has someone made an intermediate directory unsearchable? Every directory in its path should have search (i.e. execute) permission for all users. Check /usr, /usr/lib, /usr/lib/postgresql, and so on. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(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] Date trunc in UTC
On Wednesday 20 Nov 2002 10:37 am, Thrasher wrote:
> No I cannot use SET TIME ZONE.
>
> SET TIME ZONE will be set by any client backend. But what I want to get
> is that DATE_TRUNC('month', ) = DATE_TRUNC('month',
> ).
Sorry, I've obviously misunderstood. Are you just looking to discard the
timezone so they look the same?
select date_trunc('month', CAST(CURRENT_TIMESTAMP AS timestamp without time
zone));
date_trunc
-
2002-11-01 00:00:00
I'd have thought that would give you some problems around local/utc midnight
on the first of the month.
Or is it that you want to know what time it was in UTC zone at the start of
the month local time?
If I'm still being a bit slow (quite likely) can you explain what you're using
this for?
> >>=# select date_trunc ('month', now ());
> >>date_trunc
> >>
> >> 2002-11-01 00:00:00+01
> >>Instead, I would like to have as a result
> >>
> >> 2002-11-01 01:00:00+01
> >>
> >>which is correct, but I cannot set the whole server to UTC. Any way to
> >>get this ?
--
Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problems invoking psql. Help please.
My continued appreciation to Tom Lane, Michael Lange, Luis Sousa and Cameron Spitzer: Apologies for the length of this, but I've tried everything suggested and most f this is the shell dialogues of when I did so. At 08:40 AM 11/20/02 +0100, Michiel wrote: That is indeed true. Also I see you do everything as root, try creating a new user especially for postgres databases. create a directory with root in /usr/loca/pgsql/ named data, or any other directory that is in $PGDATA. chown the directory to the postgres user, log in as the postgres user and try to initdb, postmaster (-i!) and connect... I think the problem lies here somewhere... Michiel Actually I tend to use the postgres user for all of this stuff. I'm not sure why my prompt does not indicate that to be the case. Two shells working, in the first I did: postgres@biko:/usr/local$ su Password: biko:/usr/local# mkdir pgsql biko:/usr/local# cd pgsql/ biko:/usr/local/pgsql# mkdir data biko:/usr/local/pgsql# ls -al total 12 drwxr-sr-x3 root staff4096 Nov 20 02:55 . drwxrwsr-x 15 root staff4096 Nov 20 02:54 .. drwxr-sr-x2 root staff4096 Nov 20 02:55 data biko:/usr/local/pgsql# chown postgres:postgres data biko:/usr/local/pgsql# ls -al total 12 drwxr-sr-x3 root staff4096 Nov 20 02:55 . drwxrwsr-x 15 root staff4096 Nov 20 02:54 .. drwxr-sr-x2 postgres postgres 4096 Nov 20 02:55 data biko:/usr/local/pgsql# su postgres biko:/usr/local/pgsql$ In the second shell, this was the dialogue: biko:/usr/lib/postgresql/bin$ whoami postgres biko:/usr/lib/postgresql/bin$ ./initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. Fixing permissions on existing directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok creating template1 database in /usr/local/pgsql/data/base/1... ok creating configuration files... ok initializing pg_shadow... ok enabling unlimited row size for system tables... ok creating system views... ok loading pg_description... ok vacuuming database template1... ok copying template1 to template0... ok Success. You can now start the database server using: ./postmaster -D /usr/local/pgsql/data or ./pg_ctl -D /usr/local/pgsql/data -l logfile start biko:/usr/lib/postgresql/bin$ ./postmaster -i -D /usr/local/pgsql/data This is different from how I used to invoke the postmaster, but seems to work nonetheless. This sequence has permitted me to access the template0 database from pgAdmin II on my Windows desktop box again. However, when I again attempt to invoke the psql client, I get this: biko:/usr/bin$ ls -al | grep pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 createdb -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 createuser -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 dropdb -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 dropuser -> pg_wrapper lrwxrwxrwx1 root root 10 Nov 19 20:04 pg_config -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 pg_dump -> pg_wrapper lrwxrwxrwx1 root root 10 Nov 19 20:04 pg_restore -> pg_wrapper -rwxr-xr-x1 root root 6584 Sep 11 04:30 pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper biko:/usr/bin$ ./psql -U postgres template1 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin$ ./psql -U postgres template0 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin$ Following Louise Sousa's advice, here is what I saw: biko:/usr/bin$ whoami postgres biko:/usr/bin$ psql template1 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin$ psql -h biko No database specified biko:/usr/bin$ psql -h biko template1 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin$ I got the same results when I changed my pg_hba.conf file to read: local all trust #host all 127.0.0.1 255.255.255.255trust host all 192.168.2.21 255.255.255.0 trust host all 0.0.0.0 0.0.0.0 reject Invoking SQLedger does permit me to Create Dataset, but my attempt to login to that dataset gets the following error: SELECT version FROM defaults ERROR: Relation "defaults" does not exist And looking back to the pgAdmin II client, I see that the databases created by SQLedger seem to exist, but are X'd out and their tables and other objects are inaccessible. Cameron suggested I should also reinstall postgresql-client: biko:/usr/bin# whoami root biko:/usr/bin# apt-get install postgresql-client Reading Package Lists... Done Building Depe
Re: [SQL] Problems invoking psql. Help please.
Mr. Lane: pg_wrapper permits Others to Execute it. biko:/usr/bin$ ls -al | grep pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 createdb -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 createuser -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 dropdb -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 dropuser -> pg_wrapper lrwxrwxrwx1 root root 10 Nov 19 20:04 pg_config -> pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 pg_dump -> pg_wrapper lrwxrwxrwx1 root root 10 Nov 19 20:04 pg_restore -> pg_wrapper -rwxr-xr-x1 root root 6584 Sep 11 04:30 pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper biko:/usr/bin$ At 02:13 AM 11/20/02 -0500, Tom Lane wrote: Hugh Esco <[EMAIL PROTECTED]> writes: >> biko:/usr/bin# ls -al | grep psql >> lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper > This seems to say that Other users, like postgres, should be able to > execute it. I'm confused, here. The permissions attached to a symbolic link are meaningless, in all Unix variants I've dealt with. You need to look at the permissions of the linked-to object (here, pg_wrapper) instead... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problems invoking psql. Help please.
Everything in the path is executable for others. That is true for: /usr/lib/postgresql/bin and for: /usr/bin where psql is located. -- Hugh At 12:49 PM 11/20/02 +, Oliver Elphick wrote: There is something wrong with permissions here. You ought to be able, as _any_ user, to run /usr/lib/postgresql/bin/psql Can you? It seems clear that pg_wrapper can't. If not, why not? I assume the file must exist, since you have just reinstalled the package. Is the file itself executable by all users? (Use "ls -l" to check this.) Has someone made an intermediate directory unsearchable? Every directory in its path should have search (i.e. execute) permission for all users. Check /usr, /usr/lib, /usr/lib/postgresql, and so on. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 13:52, Hugh Esco wrote: > However, when I again attempt to invoke the psql client, I get this: > >biko:/usr/bin$ ./psql -U postgres template1 > >Could not execv /usr/lib/postgresql/bin/psql Pay attention to the exact message and do not flounder around aimlessly. There is no reason to be messing about with pg_hba.conf. You have some kind of system problem here. execv() is a system call to run another executable in place of the current process. If the other executable is not present, or does not have permissions, you will not be able to run it. Find out why. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 14:23, Hugh Esco wrote: > Everything in the path is executable for others. > That is true for: > /usr/lib/postgresql/bin > and for: > /usr/bin > where psql is located. So can you run the executable directly? /usr/lib/postgresql/bin/psql -d template1 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(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] Problems invoking psql. Help please.
Oliver Elphick <[EMAIL PROTECTED]> writes: > execv() is a system call to run another executable in place of the > current process. If the other executable is not present, or does not > have permissions, you will not be able to run it. Find out why. Aside from access problems for the executable itself, it could be that there's a shared-library access problem. Perhaps ldconfig needs to be told where libpq.so is? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trying to learn plpqsql... so please forgive..
Michiel, > Maybe this should be sent to novice... I was not certain, but if it > should, please tell me so. No need to apologise. Novice would have been appropriate, but SQL is OK too. > When I do an 'INSERT INTO VALUES ' > and on the table is a serial primary key named p_key. > As I want this number to be auto-generated, but use it as a 'customer > number', I want to create this function to return the value of this > insert. I thought/hoped that this would work, but as there are some > people dependant on this database, I dare not try out too much ;-) > This would be the first time I used plpgsql, so I am not so certain > about what I do. Hmmm ... the trigger, as you've written it, won't work. An INSERT trigger can modify the inserted data, or reject it, or update data in other tables. But it cannot return data to the screen. Can you break down, in more detail, what you're *trying* to do? It can probably be done, but I'm still not clear on what you're attempting. -Josh Berkus ---(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] Problems invoking psql. Help please.
On Wed, 2002-11-20 at 15:03, Tom Lane wrote: > Oliver Elphick <[EMAIL PROTECTED]> writes: > > execv() is a system call to run another executable in place of the > > current process. If the other executable is not present, or does not > > have permissions, you will not be able to run it. Find out why. > > Aside from access problems for the executable itself, it could be that > there's a shared-library access problem. Perhaps ldconfig needs to be > told where libpq.so is? It's not the error message you would get for that: olly@linda$ sudo mv /usr/lib/libpq.so.2 /usr/lib/libpq.so.2.bak Password: olly@linda$ psql -d bray /usr/lib/postgresql/bin/psql: error while loading shared libraries: libpq.so.2: cannot open shared object file: No such file or directory -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Drop NOT NULL constraint !!!
Does anybody could tell me how to drop a constraint on a column where no name was provided to the constraint? How does Pg name constraints? Thanks -- Renê Salomão Ibiz Tecnologia -- www.ibiz.com.br ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Drop NOT NULL constraint !!!
do a \d tablename for the name of the contraint. say its $1 the do psql> alter table drop contstraint "$1" RESTRICT; > Does anybody could tell me how to drop a constraint on a column where no name was >provided to > the constraint? How does Pg name constraints? > > Thanks > -- > Renê Salomão > Ibiz Tecnologia -- www.ibiz.com.br > > > ---(end of broadcast)--- TIP 6: Have >you > searched our list archives? > > http://archives.postgresql.org - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Trees: maintaining pathnames
On 17 Nov 2002 at 14:51, Josh Berkus wrote: > Dan, > > > My existing tree implementation reflects the files contained on disk. > > The > > full pathname to a particlar file is obtained from the path to the > > parent > > directory. I am now considering putting this information into a > > field in > > the table. > > > Suggestions, comment, open ridicule, most welcome. thanks. > > This is a fine implementation using the adjacency list model of tree > design. However, I think you may find that the string-based tree > implementation in /contrib/ltree is more suited to your purposes, and > easier to maintain. That looks interesting. I have installed that onto a test server and I'm playing around with it.[1] The contrib/ltree project implements a tree via text parsing. Below I show the test data it created. For my usage, I'm not sure I need it. I have implemented the "Adjacency List" tree implementation (that's what I've been told). In short, my tree contains three basic fields: id, name, parent_id. Given that I'm considering adding a new field path_name to the tree, I can't see the ltree package will give me anything more than I can get from like. My main reason for adding path_name was doing queries such as: select * from tree where path_name like '/path/to/parent/%' which will return me all the descendants of a give node (in this case '/path/to/parent/'.[2] I have discussed [offlist] the option of using a secondary table to store the pathname (i.e. a cach table) which would be updated using a loop in the tigger instead of using cascading triggers. I would prefer to keep the pathname in the same table. In my application, I have about 120,000 nodes in the tree. I am using PL/pgSQL quite a lot. Perhaps moving the triggers to C at a later date may provide a speed increase if the tree expands considerably. Also, it is noted that those triggers set the pathname twice, once in the before, and once in the after trigger. I'll try to optimize that for a future "release". ltreetest=# \d List of relations Name | Type | Owner --+---+--- test | table | dan (1 row) ltreetest=# select * from test; path --- Top Top.Science Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Hobbies Top.Hobbies.Amateurs_Astronomy Top.Collections Top.Collections.Pictures Top.Collections.Pictures.Astronomy Top.Collections.Pictures.Astronomy.Stars Top.Collections.Pictures.Astronomy.Galaxies Top.Collections.Pictures.Astronomy.Astronauts (13 rows) [1] - For other following on, I had to do the following: - downloaded the 7.2 version of the code from http://www.sai.msu.su/~megera/postgres/gist/ltree/ - installed using gmake not make - grabbed the sample file from http://developer.postgresql.org/cvsweb.cgi/pgsql- server/contrib/ltree/ltreetest.sql [2] - My application involves mirroring a file system (directories and files). FWIW, in this instances, files are not renamed, they are deleted and recreated elsewhere. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trees: maintaining pathnames
Dan Langille wrote:
Given that I'm considering adding a new field path_name to the tree,
I can't see the ltree package will give me anything more than I can
get from like. My main reason for adding path_name was doing queries
such as:
select * from tree where path_name like '/path/to/parent/%'
which will return me all the descendants of a give node (in this case
'/path/to/parent/'.[2]
FWIW, you could also do this with connectby() in contrib/tablefunc (new in
7.3; see the README for syntax details):
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '1', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+
1 | | Top
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 |10 | Stars
12 |10 | Galaxies
13 |10 | Astronauts
(13 rows)
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '6', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
(2 rows)
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '8', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+-
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 |10 | Stars
12 |10 | Galaxies
13 |10 | Astronauts
You could also do:
CREATE OR REPLACE FUNCTION node_id(text) returns int as 'select id from tree
where name = $1' language 'sql';
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id',
node_id('Science'), 0) AS c(id int, parent_id int, level int), tree t WHERE
t.id = c.id;
id | parent_id | name
+---+--
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
(4 rows)
I have discussed [offlist] the option of using a secondary table to
store the pathname (i.e. a cach table) which would be updated using a
loop in the tigger instead of using cascading triggers. I would
prefer to keep the pathname in the same table.
In my application, I have about 120,000 nodes in the tree. I am
using PL/pgSQL quite a lot. Perhaps moving the triggers to C at a
later date may provide a speed increase if the tree expands
considerably.
I've tested connectby() on a table with about 220,000 nodes. It is pretty fast
(about 1 sec to return a branch with 3500 nodes), and is entirely dynamic
(requires no triggers).
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] trying to learn plpqsql... so please forgive..
To those who pointed at the SELECT currval , thanks!, I think this is what I need instead of a trigger. So the real problem is solved I think. However I am quite curious about the plpgsql thing, I think I may need to use it, or may WANT to use it (performance wise... better to be as close as possible to the database whenever possible is one of my mottos ;->) So I will go in more detail about the case, as I am one of those people who can hardly learn from books, but far more by seeing a case and a solution... then apply it to some other problem instead... a strength and weakness in one ;-> Let's say I created this table CREATE TABLE mytable( my_key SERIAL NOT NULL PRIMARY KEY, row1 VARCHAR(5), row2 VARCHAR(15), row3 TEXT); And this function: CREATE FUNCTION add_cust() RETURNS INT4 AS ' -- SERIAL data type is really an INT4 (and some more). BEGIN RETURN NEW.my_key; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER add_cust BEFORE INSERT ON mytable FOR EACH ROW EXECUTE PROCEDURE add_cust(); Ok, now I know it won't work... the idea was to use this with PHP in a webclient interface where the customer could give some information about him/herself and then would be registered with the customer number generated by the SERIAL type. Would it work if I did a CREATE TRIGGER add_cust AFTER INSERT... ? (mention the AFTER instead of BEFORE) Please mind that the problem has now migrated to solved but still curious *g* :) Michiel At 08:49 20-11-2002 -0800, Josh Berkus wrote: Michiel, > Maybe this should be sent to novice... I was not certain, but if it > should, please tell me so. No need to apologise. Novice would have been appropriate, but SQL is OK too. > When I do an 'INSERT INTO VALUES ' > and on the table is a serial primary key named p_key. > As I want this number to be auto-generated, but use it as a 'customer > number', I want to create this function to return the value of this > insert. I thought/hoped that this would work, but as there are some > people dependant on this database, I dare not try out too much ;-) > This would be the first time I used plpgsql, so I am not so certain > about what I do. Hmmm ... the trigger, as you've written it, won't work. An INSERT trigger can modify the inserted data, or reject it, or update data in other tables. But it cannot return data to the screen. Can you break down, in more detail, what you're *trying* to do? It can probably be done, but I'm still not clear on what you're attempting. -Josh Berkus ---(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] trying to learn plpqsql... so please forgive..
Michiel, > And this function: > CREATE FUNCTION add_cust() RETURNS INT4 AS ' -- SERIAL data type is really > an INT4 (and some more). > BEGIN > RETURN NEW.my_key; > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER add_cust BEFORE INSERT ON mytable > FOR EACH ROW EXECUTE PROCEDURE add_cust(); > > Ok, now I know it won't work... the idea was to use this with PHP in a > webclient interface where the customer could give some information about > him/herself and then would be registered with the customer number generated > by the SERIAL type. > Would it work if I did a CREATE TRIGGER add_cust AFTER INSERT... ? (mention > the AFTER instead of BEFORE) No, you can't return a value to the client from a Trigger. Not ever. Triggers modify data, and they can log stuff, but they can't return values to the calling interface. Now, what you could do is replace the whole insert with a function, doing: SELECT add_cust( name, address, phone, credit_card); Which does the inserting and returns the new id to the client.This is a solution I frequently use in my web apps. -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] why the difference?
Hi folk, i am finding something mysterious in SQL can anyone explain? consider the SQL: tradein_clients=# select distinct on (amount,co_name,city) category_id,amount,co_name,city from eyp_listing where keywordidx ## 'vegetable' and category_id=781 ; category_id | amount | co_name |city -++---+ 781 | 0 | ANURADHA EXPORTS | CHENNAI 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | TRIMA ENTERPRISES | CHENNAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT| COIMBATORE (6 rows) lets remove the contraint "category_id=781" and store the output in a table "t_a". tradein_clients=# CREATE TABLE t_a AS select distinct on (amount,co_name,city) category_id,amount,co_name,city from eyp_listing where keywordidx ## 'vegetable' ; then when i select from t_a with category_id=781 i have less secords tradein_clients=# SELECT * from t_a where category_id=781; category_id | amount | co_name |city -++---+ 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT| COIMBATORE (4 rows) Can anyone please explain the difference? Regds Mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
