[GENERAL] newbie question
Hi Does Postgresql utilize hyperthreading and multiple cores on the relevant processors on windows? Thanks H.F.
Re: [GENERAL] newbie question
On Mon, Jun 6, 2011 at 4:17 PM, Heine Ferreira heine.ferre...@gmail.comwrote: Hi Does Postgresql utilize hyperthreading and multiple cores on the relevant processors on windows? Thanks H.F. It certainly looks like it on my machine! As far as my Linux box can tell, the hyperthreaded CPUs just appear to be additional CPUs. So even though I only have four physical CPUs in my box, Linux thinks I have eight. -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney.
Re: [GENERAL] newbie question
PostgreSQL is process based. So it can be configured to use resources as intensively as you like. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Heine Ferreira Sent: Monday, June 06, 2011 1:17 PM To: pgsql-general@postgresql.org Subject: [GENERAL] newbie question Hi Does Postgresql utilize hyperthreading and multiple cores on the relevant processors on windows? Thanks H.F.
Re: [GENERAL] newbie question
On 06/07/2011 04:17 AM, Heine Ferreira wrote: Hi Does Postgresql utilize hyperthreading and multiple cores on the relevant processors on windows? Sort-of. PostgreSQL runs many processes, one per query. Each process has a single thread. This means that one query can use at most one CPU core, but many queries running concurrently can use many cpu cores. For future posts, please choose a more informative subject line. Many people will ignore posts entitled help, question, etc. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] newbie question - delete before insert
Grant Mckenzie wrote on 20.11.2010 07:00: How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? You can simply send the UPDATE, if nothing was updated, it's safe to send the INSERT Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] newbie question - delete before insert
On 11/20/2010 02:43 PM, Thomas Kellerer wrote: Grant Mckenzie wrote on 20.11.2010 07:00: How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? You can simply send the UPDATE, if nothing was updated, it's safe to send the INSERT Regards Thomas something on the line of http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html *Example 38-2. Exceptions with UPDATE/INSERT* -- With Regards Ashish Karalkar
Re: [GENERAL] newbie question - delete before insert
How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? I tried using an insert rule to delete any existing rows first then insert however this leads to infinitely recursive rules ( which postgres properly rejects. ) I'm guesisng that the most sensible approach would be a stored proc/function? I've done it with an on insert trigger. The where clause contains the values that make this row unique. If it finds another row with the same fields, it doe san update instead of the insert: CREATE OR REPLACE FUNCTION stock.trg_beforeinsertstock() RETURNS trigger AS $BODY$ declare v_stockid int; begin select stockid into v_stockid from stock where pnid=new.pnid and ownerid=new.ownerid and coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1) and coalesce(batchid,-1)=coalesce(new.batchid,-1); if v_stockid is not null then Update stock set stock=stock+new.stock where stockid=v_stockid; return null; else return new; end if; end; $BODY$ LANGUAGE plpgsql VOLATILE; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] newbie question - delete before insert
Hi, a question that I imagine is a faq but have not been able to find much help. How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? I tried using an insert rule to delete any existing rows first then insert however this leads to infinitely recursive rules ( which postgres properly rejects. ) I'm guesisng that the most sensible approach would be a stored proc/function? Cheers G. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] newbie question
Hi, In the mid 1990's I used the Progress Database which had a great feature where the it was possible to set the software version in the configuration file. For example, if you had a built a database and application using version 3 and then upgraded the version 7, it was possible to set the database configuration to version 3 and then continue to use the same database and applications without having to dump the database tables and data then import them into a version 7 database or update applications. Does this feature exist in ProgreSQL, can a v8 access a database created using v7? -- Regards Russell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] newbie question
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Russell Aspinwall Sent: woensdag 17 oktober 2007 9:34 To: pgsql-general@postgresql.org Subject: [GENERAL] newbie question Hi, [snip] For example, if you had a built a database and application using version 3 and then upgraded the version 7, it was possible to set the database configuration to version 3 and then continue to use the same database and applications without having to dump the database tables and data then import them into a version 7 database or update applications. Does this feature exist in ProgreSQL, can a v8 access a database created using v7? No it cannot. You must perform a dump and restore. Also note that between different architectures (and sometimes between different compiles) the file format might also be different. See also: http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html. However all queries running on v7 should work on v8. The application should not require any modifications. In practice you should, of course, test that before putting it into production. - Joris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] newbie question
-Original Message- From: Russell Aspinwall [mailto:[EMAIL PROTECTED] Sent: woensdag 17 oktober 2007 11:37 To: Joris Dobbelsteen Subject: Re: [GENERAL] newbie question Joris Dobbelsteen wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Russell Aspinwall Sent: woensdag 17 oktober 2007 9:34 To: pgsql-general@postgresql.org Subject: [GENERAL] newbie question Hi, [snip] For example, if you had a built a database and application using version 3 and then upgraded the version 7, it was possible to set the database configuration to version 3 and then continue to use the same database and applications without having to dump the database tables and data then import them into a version 7 database or update applications. Does this feature exist in ProgreSQL, can a v8 access a database created using v7? No it cannot. You must perform a dump and restore. Also note that between different architectures (and sometimes between different compiles) the file format might also be different. See also: http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html. However all queries running on v7 should work on v8. The application should not require any modifications. In practice you should, of course, test that before putting it into production. Thank you for the reply, could this feature be added in future? That's not a question for me, but rather for the people who spend a lot of time creating this excellent database server. Nevertheless, given that it: * significantly complicates the software * might hinder (some) new features to be implemented * there is little demand, hence nobody is willing to spend the incredible amount of time on programming maintenance * Binary format already differs between different processor architectures/compliations * the SQL interface itself already shouldn't change (so from the application point of view) I would say that such a feature is highly unlikely that it will be added in the foreseeable future. A more likely feature is inplace/live (whatever you call it) upgrades of the data to a newer version. - Joris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie question about importing text files...
On Tue, 2006-10-10 at 04:16, Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/09/06 22:43, Jonathan Greenberg wrote:
So I've been looking at the documentation for COPY, and I'm curious about a
number of features which do not appear to be included, and whether these
functions are found someplace else:
1) How do I skip an arbitrary # of header lines (e.g. 1 header line) to
begin reading in data?
Using something like bash, you can do this:
tail -n $(( `wc -l bookability-pg.sql|grep -oP [0-9]+` -2 ))
bookability-pg.sql|wc -l
make it an alias and call it skip and have it take an argument:
Put this in .bashrc and run the .bashrc file ( . ~/.bashrc ):
skipper(){
tail -n $(( `wc -l $1|grep -oP [0-9]+` -$2 )) $1
}
2) Is it possible to screen out lines which begin with a comment character
(common outputs for csv/txt files from various programs)?
grep -vP ^# filename
will remove all lines that start with #. grep is your friend in unix.
If you don't have unix, get cygwin as recommended elsewhere.
3) Is there a way to read in fixed width files?
If you don't mind playing about with sed, you could use it and bash
scripting to do it. I have before. It's ugly looking but easy enough
to do. But I'd recommend a beginner use a scripting language they like,
one of the ones that starts with p is usually a good choice (perl,
python, php, ruby (wait, that's not a p!) etc...)
Both Python Perl have CSV parsing modules, and can of course deal
with fixed-width data, let you skip comments, commit every N rows,
skip over committed records in can the load crashes, etc, etc, etc.
php has a fgetcsv() built in as well. It breaks down csv into an array
and is really easy to work with.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie question about importing text files...
On 10/10/06, Jonathan Greenberg [EMAIL PROTECTED] wrote: So I've been looking at the documentation for COPY, and I'm curious about a number of features which do not appear to be included, and whether these functions are found someplace else: 1) How do I skip an arbitrary # of header lines (e.g. 1 header line) to begin reading in data? if in 'csv' mode, you can set the header flag. if not, standard unix tools fit the bill: cat import.txt | tail -n +2 output.txt -- from bash copy table foo from '/home/import.txt'; on windows? get cygwin! or, if you prefer a more sql-ish solution, load your text data into scratch tables (all text fields) as is and do filtering there. this works pretty well actually. copy table foo from '/home/import.txt'; create table bar as select * from foo offset 3; theres a million way to do this, most inolve processing before or after the copy statement, unless you happen to be importing csv (often, but not always works as is) or data generated from postgresql. 2) Is it possible to screen out lines which begin with a comment character (common outputs for csv/txt files from various programs)? see notes above. 1. import all data to scratch table 2. use sql alternative: master perl (i prefer sql approach usually, perl scares me!) 3) Is there a way to read in fixed width files? here again you could load the data into postgresql, one field per row even, and process as such create table import(bigfield text); copy tabe import from 'foo.txt'; create table foo as select substr(bigfield, 1, 3)::int as a, substr(bigfield, 4, 2)::char(2) as b, [...]; voila! merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Newbie question about importing text files...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/10/06 01:44, Merlin Moncure wrote: On 10/10/06, Jonathan Greenberg [EMAIL PROTECTED] wrote: So I've been looking at the documentation for COPY, and I'm curious about a number of features which do not appear to be included, and whether these functions are found someplace else: 1) How do I skip an arbitrary # of header lines (e.g. 1 header line) to begin reading in data? if in 'csv' mode, you can set the header flag. if not, standard unix tools fit the bill: cat import.txt | tail -n +2 output.txt -- from bash That's not a very efficient way to handle large data sets. - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFK2HGS9HxQb37XmcRAtxxAKCLKYg/RU23PUbmxQvl8MQVnVw/7ACgtwjj eZJX4zGcck0Zq8INAHXEYlA= =9EwP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Newbie question about importing text files...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/09/06 22:43, Jonathan Greenberg wrote: So I've been looking at the documentation for COPY, and I'm curious about a number of features which do not appear to be included, and whether these functions are found someplace else: 1) How do I skip an arbitrary # of header lines (e.g. 1 header line) to begin reading in data? 2) Is it possible to screen out lines which begin with a comment character (common outputs for csv/txt files from various programs)? 3) Is there a way to read in fixed width files? Both Python Perl have CSV parsing modules, and can of course deal with fixed-width data, let you skip comments, commit every N rows, skip over committed records in can the load crashes, etc, etc, etc. Probably not what you wanted to read, though, since performance takes a big hit. But it definitely works... - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFK2UAS9HxQb37XmcRAmOqAJ4um4xLJnKBVQ2LWB1kYYIJyBNHZgCgv/gk griA2wHFOEogj2WToM5mxpc= =gn/u -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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
[GENERAL] Newbie question about importing text files...
So I've been looking at the documentation for COPY, and I'm curious about a number of features which do not appear to be included, and whether these functions are found someplace else: 1) How do I skip an arbitrary # of header lines (e.g. 1 header line) to begin reading in data? 2) Is it possible to screen out lines which begin with a comment character (common outputs for csv/txt files from various programs)? 3) Is there a way to read in fixed width files? Thanks! --j -- Jonathan A. Greenberg, PhD NRC Research Associate NASA Ames Research Center MS 242-4 Moffett Field, CA 94035-1000 Office: 650-604-5896 Cell: 415-794-5043 AIM: jgrn307 MSN: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Newbie Question: FAQ for database optimization?
On Tue, Dec 20, 2005 at 10:21:54PM +0100, Alexander Scholz wrote: Hi, is there a newbie's FAQ / book / link for howto optimize databases with PostgreSQL? Background: Customer has the Windows* (sorry g) Postgres 8.1.0 standard installation out of the box. A table has 2.5 mio records. No indizes defined, primary key (sequence) does exist. In pgAdmin select count(*) takes over 30 seconds, That sounds about right. If you want to cache this result, there are ways to do that, and there are approximations to the result if you're interested in such things. an update affecting 70'000 records takes minutes... An index on the (set of) column(s) the WHERE clause refers to would very likely help. For example, if your update looks like: UPDATE foo SET bar = 555 WHERE baz = 'blurf'; You could get some mileage out of indexing the baz column. See the docs on CREATE INDEX for the syntax. I am sure PostgreSQL could do better, we just need to tune the database. (I hope so at least!) What action and/or reading can you recommend? (We quickly need some 'wow' effects to keep the customer happy sigh). There are archives of the pgsql-performance mailing list at http://archves.postresql.org/ for a lot of this. For things you don't find there, you can either post here or go to irc://irc.freenode.net/postgresql, where there are friendly, helpful people, and occasionally Yours Truly. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Newbie Question: FAQ for database optimization?
Hi, is there a newbie's FAQ / book / link for howto optimize databases with PostgreSQL? Background: Customer has the Windows* (sorry g) Postgres 8.1.0 standard installation out of the box. A table has 2.5 mio records. No indizes defined, primary key (sequence) does exist. In pgAdmin select count(*) takes over 30 seconds, an update affecting 70'000 records takes minutes... I am sure PostgreSQL could do better, we just need to tune the database. (I hope so at least!) What action and/or reading can you recommend? (We quickly need some 'wow' effects to keep the customer happy sigh). Thanx, Alexander. *) sorry, I don't have server's hardware spec. available right now, but the MSSQL2005 instance on it does the same things in a few seconds... ;-) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Newbie Question: FAQ for database optimization?
am 20.12.2005, um 22:21:54 +0100 mailte Alexander Scholz folgendes: Hi, is there a newbie's FAQ / book / link for howto optimize databases with PostgreSQL? 07:12 rtfm_please For information about tuning 07:12 rtfm_please see http://www.powerpostgresql.com 07:12 rtfm_please or http://www.powerpostgresql.com/PerfList 07:12 rtfm_please or http://www.varlena.com/varlena/GeneralBits/116.php Background: Customer has the Windows* (sorry g) Postgres 8.1.0 standard installation out of the box. A table has 2.5 mio records. No indizes defined, primary key (sequence) does exist. In pgAdmin select count(*) bad ugly What action and/or reading can you recommend? (We quickly need some 'wow' effects to keep the customer happy sigh). Create suitable indexes. 07:14 akretschmer ??index 07:14 rtfm_please For information about index 07:14 rtfm_please see http://www.postgresql.org/docs/current/static/indexes-expressional.html 07:14 rtfm_please or http://www.postgresql.org/docs/current/static/indexes-partial.html 07:14 rtfm_please or http://www.postgresql.org/docs/current/static/indexes.html Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] newbie question: reading sql commands from script
Thanks to all who answered this question! From: Brent Wood [EMAIL PROTECTED] To: basel novo [EMAIL PROTECTED] CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] newbie question: reading sql commands from script Date: Wed, 26 Oct 2005 10:36:34 +1300 (NZDT) On Tue, 25 Oct 2005, basel novo wrote: What is the equivalent of the mysql 'source' command for reading sql commands from ascii script files? I have not used mysql, so am not familiar with the source command, but to have postgres run a set of sql statements/queries from a file you can: psql DB -f filename also, to run a single command from a script (or commandline) psql DB -c sql command Cheers, Brent Wood ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] newbie question: reading sql commands from script
On Tue, 25 Oct 2005, basel novo wrote: What is the equivalent of the mysql 'source' command for reading sql commands from ascii script files? I have not used mysql, so am not familiar with the source command, but to have postgres run a set of sql statements/queries from a file you can: psql DB -f filename also, to run a single command from a script (or commandline) psql DB -c sql command Cheers, Brent Wood ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] newbie question: reading sql commands from script
On Mon, 2005-10-24 at 20:39 -0400, Sean Davis wrote: In psql, look at \i. Sean - Original Message - From: basel novo To: pgsql-general@postgresql.org Sent: Monday, October 24, 2005 8:28 PM Subject: [GENERAL] newbie question: reading sql commands from script files What is the equivalent of the mysql 'source' command for reading sql commands from ascii script files? Also, for shell scripting, there are command line options: psql -f filename or redirection: psql filename (the former gives line numbers), or for a single command: psql -c sql command -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] newbie question: reading sql commands from script files
What is the equivalent of the mysql 'source' command for reading sql commands from ascii script files? Thanks. _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] newbie question: reading sql commands from script files
Title: [GENERAL] newbie question: reading sql commands from script files In psql, look at \i. Sean - Original Message - From: basel novo To: pgsql-general@postgresql.org Sent: Monday, October 24, 2005 8:28 PM Subject: [GENERAL] newbie question: reading sql commands from script files What is the equivalent of the mysql 'source' command for reading sql commands from ascii script files? Thanks. _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie question on RULEs .. or .. bug ?
Hello Tom, Thank you for the enlightment, I think I understand what you say. There are however a few things I'm not sure about still. The update seems to work as I would expect when I include one or more in there where clause from the primary key. If I have a field not in the primary key included in the where, I don't get anything updated, e.g. update tasks set seq = 2 where id = 87 and name = '2WWE'; does nothing !?? Two questions: 1) Is there any way to define a view like this where I can insert and update without these rules, just as if it was one real table ? 2) If I need to use rules to do update/insert on tasks, how can I make it 'transparent' as in the above example (the update that does nothing) ? Greetings, Leif On Tue, 17 May 2005, Tom Lane wrote: Leif Jensen [EMAIL PROTECTED] writes: CREATE RULE update_tasks2taskshead AS ON UPDATE TO tasks WHERE NEW.seq = 0 DO NOTHING ; That rule looks a bit useless ... Yeah, just disabled for now ;-) CREATE RULE update_tasks2ganntinfo AS ON UPDATE TO tasks DO INSTEAD ( update ganntinfo set id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name -- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category ; ) ; You definitely need a WHERE clause in that rule; otherwise you get exactly the result you saw: all rows of ganntinfo are updated. The comment in the manual about the original WHERE clause really means that the values of NEW will be constrained to take on only the values determined by the original WHERE. Your update is basically a join of ganntinfo with the subset of the tasks view determined by the original WHERE --- so you have to constrain ganntinfo too. I suppose that you want something like update ganntinfo set category = NEW.category, name = NEW.name WHERE id = NEW.id AND seq = NEW.seq ; since id/seq is your primary key for ganntinfo. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Newbie question
hi, is it possible to schedule the execution of an sql stored procedure in postgress on linux? thanks Hugo ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Newbie question
Sure. Just set up psql with a SQL script containing your task using a chron job. The psql utility will accept a command file as a parameter. PostgreSQL has functions rather than stored procedures, but it works out about the same. You could also put a bunch of SQL commands that you want to run in the file that you feed to psql. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Hugo Sent: Wednesday, May 18, 2005 8:24 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Newbie question hi, is it possible to schedule the execution of an sql stored procedure in postgress on linux? thanks Hugo ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Newbie question
IMHO cron would be the best for that... is it possible to schedule the execution of an sql stored procedure in postgress on linux? -- Vlad ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Newbie question
Dear Hugo, It is possible using cron and psql. Just man cron on unix if you're not already familiar with that. The command line you should use is something like: psql [connection options] -f sql_commands_file.sql Also man psql for the options you can use... Starting the procedure should be done by something like: SELECT procedure_name(); in the sql commands file given to psql. Alternatively you can do: echo SELECT procedure_name(); | psql [connection options] but I don't know how well that works with cron... I'm not actually using it... HTH, Csaba. On Wed, 2005-05-18 at 17:23, Hugo wrote: hi, is it possible to schedule the execution of an sql stored procedure in postgress on linux? thanks Hugo ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Newbie question
Hi Hugo, Yes, it is possible. Just write an outer perl script(in example), which to call this stored procedure. Then put in the cron to call whis perl script. As Far As I Know there isn't other way. Kaloyan Iliev Hugo wrote: hi, is it possible to schedule the execution of an sql stored procedure in postgress on linux? thanks Hugo ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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: [GENERAL] Newbie question
thanks all for your help, I'll try your suggestions regards Hugo 2005/5/18, Kaloyan Iliev Iliev [EMAIL PROTECTED]: Hi Hugo, Yes, it is possible. Just write an outer perl script(in example), which to call this stored procedure. Then put in the cron to call whis perl script. As Far As I Know there isn't other way. Kaloyan Iliev Hugo wrote: hi, is it possible to schedule the execution of an sql stored procedure in postgress on linux? thanks Hugo ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Newbie question
Hugo wrote: hi, is it possible to schedule the execution of an sql stored procedure in postgress on linux? thanks Hugo cron job: eg. Sat 2:30am 30 2 * * Sat psql -d dbname -c select your_func() -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Newbie question
Kaloyan Iliev Iliev wrote: Hi Hugo, Yes, it is possible. Just write an outer perl script(in example), which to call this stored procedure. Then put in the cron to call whis perl script. As Far As I Know there isn't other way. Depending on whether you want to schedule a procedure on a regular basis or not. You could us 'at' to schedule a one time procedure execution. -- Until later, Geoffrey ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Newbie question on RULEs .. or .. bug ?
Hello, I have been working with a great database system called PostgreSQL for many years ;-) but never had to use any RULEs. I now have to use and update through a view and have written a few rules to make this possible as per the manual. My insert rules seems to work fine, but I can't make the update rule do what I want and as I believe stated in the manual. In the attached schema I have 2 basic tables and a view combining those. I have 2 rules for insert on the view and these works fine. The update rule, however, doesn't do what I want. The manual states that the origsal query tree (where) is added to the rule qualification, so I would exspect the update statement below would only update 1 row of the ganntinfo table, but it updates all 5 ? What am I doing wrong ? projtaskdb=# SELECT * FROM ganntinfo ; id | seq | category | name -+-+--+- 46 | 0 | SRC | 2WWE 172 | 0 | SRC | RKD60 138 | 0 | SRC | Diverse 34 | 0 | SRC | VF 87 | 0 | SRC | 2WWE (5 rows) projtaskdb=# UPDATE tasks SET id = 87, category = 'SRC', name = '2WWE', customer = 'Custm', description = 'test' WHERE id = 87 and category = 'SRC'; UPDATE 5 projtaskdb=# SELECT * FROM ganntinfo ; id | seq | category | name +-+--+-- 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE (5 rows) projtaskdb=# I tried to put an 'explain' in front of the update within the update rule, but got a syntax error. Why is that ? Please help, Leif SET SESSION AUTHORIZATION 'dba'; CREATE TABLE ganntinfo ( id integer NOT NULL, seq smallint, category character varying(20), name character varying(40) -- PRIMARY KEY( id, seq ) ); CREATE TABLE taskshead ( id integer PRIMARY KEY, category character varying(20), subject character varying(40), customer character varying(40), description character varying(400) ); CREATE VIEW tasks ( id, seq, category, name, subject, customer, description ) AS SELECT g.id, g.seq, g.category, g.name, h.subject, h.customer, h.description FROM taskshead h LEFT OUTER JOIN ganntinfo g USING ( id, category ) -- WHERE g.seq = 0 AND g.id = h.id AND g.category = h.category ; CREATE RULE insert_tasks2taskshead AS ON INSERT TO tasks WHERE NEW.seq = 0 DO ( insert into taskshead values ( NEW.id, NEW.category, NEW.subject, NEW.customer, NEW.description ); ) ; CREATE RULE insert_tasks2ganntinfo AS ON INSERT TO tasks DO INSTEAD ( insert into ganntinfo values ( NEW.id, NEW.seq, NEW.category, NEW.name ); ) ; CREATE RULE update_tasks2taskshead AS ON UPDATE TO tasks WHERE NEW.seq = 0 DO NOTHING ; CREATE RULE update_tasks2ganntinfo AS ON UPDATE TO tasks DO INSTEAD ( update ganntinfo set id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name -- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category ; ) ; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Newbie question on RULEs .. or .. bug ?
Leif Jensen [EMAIL PROTECTED] writes: CREATE RULE update_tasks2taskshead AS ON UPDATE TO tasks WHERE NEW.seq = 0 DO NOTHING ; That rule looks a bit useless ... CREATE RULE update_tasks2ganntinfo AS ON UPDATE TO tasks DO INSTEAD ( update ganntinfo set id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name -- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category ; ) ; You definitely need a WHERE clause in that rule; otherwise you get exactly the result you saw: all rows of ganntinfo are updated. The comment in the manual about the original WHERE clause really means that the values of NEW will be constrained to take on only the values determined by the original WHERE. Your update is basically a join of ganntinfo with the subset of the tasks view determined by the original WHERE --- so you have to constrain ganntinfo too. I suppose that you want something like update ganntinfo set category = NEW.category, name = NEW.name WHERE id = NEW.id AND seq = NEW.seq ; since id/seq is your primary key for ganntinfo. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Please excuse my ignorance of databases and black holes. I don't have access to a Postgres db right now so I tried an experiment with mysql. Since they don't have a select into that creates a table, I tried this: mysql create table t as ( select * from table_x); since table_x has no rows, I get: Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql show tables; | Tables_in_test| | table_x| | t| +--+ 2 rows in set (0.02 sec) So it creates a table called t with no records and the same structure as table_x. That's what I thought the postgresql SELECT INTO would do. Now that I looked at the documentation more closely, I see that SELECT INTO returns a table when used in a query but an array of values when used in plpgsql, so that's at least part of what I have wrong. Having given it more thought, I think another error was to not declare the function as returning SETOF, so I can give that a try later. The declarations section of the pl/pgsql documentation doesn't explain how to declare a variable to represent a set of rows so if anyone can suggest something that would be helpful. Thanks. On Tue, 30 Nov 2004 22:58:11 -0500, Tom Lane [EMAIL PROTECTED] wrote: Larry White [EMAIL PROTECTED] writes: I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. How exactly would SELECT INTO return 0 rows? Perhaps the target variables vanish into a black hole? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Larry White [EMAIL PROTECTED] writes: mysql create table t as ( select * from table_x); That works in Postgres too. The SELECT INTO construct is a bit broken since, as you discovered, it has a different meaning in plpgsql than in the main SQL language. So I recommend using CREATE TABLE AS when you want to create a table this way. The declarations section of the pl/pgsql documentation doesn't explain how to declare a variable to represent a set of rows You can't. Possibly a cursor would help? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Newbie question: returning rowtypes from a plpgsql function
I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. Am I doing something wrong or is this the expected behavior? Is there a standard way to code around this? I expected my client code to check the number of rows returned to decide what to do next. thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Larry White [EMAIL PROTECTED] writes: I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. How exactly would SELECT INTO return 0 rows? Perhaps the target variables vanish into a black hole? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Newbie Question, how to grant permissions on all tables in schema/db
Hi guys, I'm wondering what the easy way to grant user/group access on all tables in a db. I just migrated about 200 tables from my MySQL database using a cool migration script, but now all the tables are owned by me in the schema public. That is all fine and all, but I wish I could run a command like: grant all on * to group developers; where developers contain my created users. is there a way to do this easily? Thanks in advance! -Kenji ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Newbie Question, how to grant permissions on all tables in schema/db
On Mon, Nov 08, 2004 at 05:00:49PM -0800, Kenji Morishige wrote: I'm wondering what the easy way to grant user/group access on all tables in a db. This comes up from time to time -- search the archives for phrases like grant and all tables. The responses usually suggest writing a script or function to query the system catalogs for the list of tables, views, sequences, etc. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Newbie question about escaping in a function
Try using EXECUTE. http://www.postgresql.org/docs/7.4/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Oct 26, 2004, at 11:57 AM, Naeem Bari wrote: I have a simple function defined thusly: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS ' DECLARE tdat timestamp; rdat timestamp; BEGIN IF ($1 IS NULL) THEN TDAT := NOW(); ELSE TDAT := $1; END IF; select tdat + interval ''$2 $3'' into rdat; return rdat; END; ' LANGUAGE 'plpgsql' VOLATILE; The problem is the interval part. How do I tell the bugger to use the second and third params as input to interval? I have tried different ways of escaping, from \$2 $3\ to $2 $3 and everything else in between, it just doesnt like it. Help! J Thanks, naeem ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Newbie question about casting literals - oracle/postgres
hi, I think it works for me. what version of postgres do you have? maybe you just need to upgrade : ) test=# select cust_id, 'TEST' as test, cust_address from customers; cust_id | test | cust_address +--+-- 11 | TEST | 200 Maple Lane 12 | TEST | 333 South Lake Drive 13 | TEST | 1 Sunny Place 14 | TEST | 829 Riverside Drive 15 | TEST | 4545 53rd Street 16 | TEST | (6 rows) test=# select version(); version PostgreSQL 7.3.7 on powerpc-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r4, propolice) (1 row) hth, dianne On Oct 26, 2004, at 6:24 PM, Naeem Bari wrote: Ok, I have a query that runs fine in oracle: select driver_id, 'GREEN' as color, pos_date, pos_lat, pos_lon from driver_pos where driver_id = 1 order by pos_date The only way this works in postgres is by casting GREEN to text using GREEN::text The problem is then this does not work with oracle. Since my software has to support both databases, I am left in a bit of a bind. Any ideas on how to make postgres accept GREEN as text without my having to spell it out by casting? Thanks! naeem ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Newbie question about casting literals - oracle/postgres
On Tue, Oct 26, 2004 at 08:24:56PM -0500, Naeem Bari wrote:
The only way this works in postgres is by casting 'GREEN' to text using
'GREEN'::text
The problem is then this does not work with oracle.
So use a standards-conformant cast, like
cast('GREEN' as text)
--
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes. (http://slashdot.org/comments.pl?sid=44793cid=4647152)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Newbie question about escaping in a function
I have a simple function defined thusly: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS ' DECLARE tdat timestamp; rdat timestamp; BEGIN IF ($1 IS NULL) THEN TDAT := NOW(); ELSE TDAT := $1; END IF; select tdat + interval ''$2 $3'' into rdat; return rdat; END; ' LANGUAGE 'plpgsql' VOLATILE; The problem is the interval part. How do I tell the bugger to use the second and third params as input to interval? I have tried different ways of escaping, from \$2 $3\ to $2 $3 and everything else in between, it just doesnt like it. Help! J Thanks, naeem
Re: [GENERAL] Newbie question about escaping in a function
On Tue, 2004-10-26 at 11:57 -0500, Naeem Bari wrote: I have a simple function defined thusly: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS ' DECLARE tdat timestamp; rdat timestamp; BEGIN IF ($1 IS NULL) THEN TDAT := NOW(); ELSE TDAT := $1; END IF; It's neater to use the COALESCE() function, which is designed expressly for this. select tdat + interval ''$2 $3'' into rdat; In PL/pgSQL that should be select into rdat ...; but that won't work in any case because you can't use passed parameters inside a string like that. return rdat; END; ' LANGUAGE 'plpgsql' VOLATILE; The problem is the interval part. How do I tell the bugger to use the second and third params as input to interval? I have tried different ways of escaping, from \$2 $3\ to $2 $3 and everything else in between, it just doesnt like it. You have to construct a command string and use EXECUTE: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS 'DECLARE tdatTIMESTAMP; result RECORD; cmd TEXT; BEGIN tdat := COALESCE($1, NOW()); cmd := ''SELECT '' || quote_literal(tdat) || ''::TIMESTAMP + INTERVAL '' || quote_literal($2 || '' '' || $3) || '' AS x''; FOR result IN EXECUTE cmd LOOP return result.x; END LOOP; END; ' LANGUAGE 'plpgsql' VOLATILE; -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels. Mark 8:38 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Newbie question about escaping in a function
Thanks! Now I get it... naeem -Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 3:05 PM To: Naeem Bari Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Newbie question about escaping in a function On Tue, 2004-10-26 at 11:57 -0500, Naeem Bari wrote: I have a simple function defined thusly: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS ' DECLARE tdat timestamp; rdat timestamp; BEGIN IF ($1 IS NULL) THEN TDAT := NOW(); ELSE TDAT := $1; END IF; It's neater to use the COALESCE() function, which is designed expressly for this. select tdat + interval ''$2 $3'' into rdat; In PL/pgSQL that should be select into rdat ...; but that won't work in any case because you can't use passed parameters inside a string like that. return rdat; END; ' LANGUAGE 'plpgsql' VOLATILE; The problem is the interval part. How do I tell the bugger to use the second and third params as input to interval? I have tried different ways of escaping, from \'$2 $3\' to ''$2 $3'' and everything else in between, it just doesn't like it. You have to construct a command string and use EXECUTE: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS 'DECLARE tdatTIMESTAMP; result RECORD; cmd TEXT; BEGIN tdat := COALESCE($1, NOW()); cmd := ''SELECT '' || quote_literal(tdat) || ''::TIMESTAMP + INTERVAL '' || quote_literal($2 || '' '' || $3) || '' AS x''; FOR result IN EXECUTE cmd LOOP return result.x; END LOOP; END; ' LANGUAGE 'plpgsql' VOLATILE; -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels. Mark 8:38 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Newbie question about casting literals - oracle/postgres
Ok, I have a query that runs fine in oracle: select driver_id, 'GREEN' as color, pos_date, pos_lat, pos_lon from driver_pos where driver_id = 1 order by pos_date The only way this works in postgres is by casting GREEN to text using GREEN::text The problem is then this does not work with oracle. Since my software has to support both databases, I am left in a bit of a bind. Any ideas on how to make postgres accept GREEN as text without my having to spell it out by casting? Thanks! naeem
Re: [GENERAL] Newbie question about casting literals - oracle/postgres
The only way this works in postgres is by casting GREEN to text using GREEN::text The problem is then this does not work with oracle. Since my software has to support both databases, I am left in a bit of a bind. Any ideas on how to make postgres accept GREEN as text without my having to spell it out by casting? You could create separate views for both. That means you would have to keep seperate views for each database but the SQL in your code would be the same. Sincerely, Joshua D. Drake Thanks! naeem -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Re: [GENERAL] Newbie question: OT
Robin 'Sparky' Kopetzky [EMAIL PROTECTED] writes: How do you pronounce PostgreSQl?? post-gres or post-gres-cue-ell. See item 1.1 of the FAQ ;-) There used to be a .wav file on the website, but I can't find it at the moment. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] newbie question... how do I get table structure?
Aaron Bratcher wrote:
Is there no way I can do it with a standard select command in a
different client? I don't need the indexes, just the column names/types.
For PostgreSQL 7.3 and above:
SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM
pg_catalog.pg_class c INNER JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN
pg_catalog.pg_attribute a ON (a.attrelid = c.oid)
WHERE
n.nspname = '{schema_name}' AND
c.relname = '{table_name}' AND
a.attisdropped = false AND
a.attnum 0
Replace {schema_name} and {table_name}.
--
Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk
Web Developer Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] newbie question... how do I get table structure?
this should work (don't forget to replace TABLE NAME!!!): SELECT A.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) AS typeName FROM pg_class C, pg_attribute A, pg_type T WHERE C.relname ILIKE 'TABLE NAME' AND (C.oid=A.attrelid) AND (T.oid=A.atttypid) AND (A.attnum0) AND (NOT A.attisdropped) ORDER BY A.attnum; Does anyone know if the ansi sql standard defines any way to do this? I've seen the DESCRIBE TABLE/INDEX/... or SHOW TABLE/INDEX/... commands in other databases, but I don't really know if they are extensions or not. On Fri, 2004-02-06 at 11:10, Aaron Bratcher wrote: Is there no way I can do it with a standard select command in a different client? I don't need the indexes, just the column names/types. -- Aaron Bratcher ab DataTools http://www.abDataTools.com On Feb 6, 2004, at 8:12 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Joshua D. Drake [EMAIL PROTECTED] writes: Aaron Bratcher wrote: What command can I use to get the structure of a given table? If psql is client \d tablename Without psql you can use pg_dump -s DBNAME -t TBLNAME from your shell prompt. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings signature.asc Description: This is a digitally signed message part
Re: [GENERAL] newbie question... how do I get table structure?
try something like this: select attname from pg_class, pg_attribute where relname='your_tablename' and attrelid=relfilenode; -- Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany http://www.topconcepts.com Tel. +49 4141 991230 mail: [EMAIL PROTECTED] Fax. +49 4141 991233 Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de System-Partner gesucht: http://www.franchise.city-map.de Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Aaron Bratcher Gesendet: Freitag, 6. Februar 2004 15:10 An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] newbie question... how do I get table structure? Is there no way I can do it with a standard select command in a different client? I don't need the indexes, just the column names/types. -- Aaron Bratcher ab DataTools http://www.abDataTools.com On Feb 6, 2004, at 8:12 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Joshua D. Drake [EMAIL PROTECTED] writes: Aaron Bratcher wrote: What command can I use to get the structure of a given table? If psql is client \d tablename Without psql you can use pg_dump -s DBNAME -t TBLNAME from your shell prompt. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] newbie question... how do I get table structure?
On Friday 06 February 2004 15:00, Franco Bruno Borghesi wrote: Does anyone know if the ansi sql standard defines any way to do this? I've seen the DESCRIBE TABLE/INDEX/... or SHOW TABLE/INDEX/... commands in other databases, but I don't really know if they are extensions or not. There's the new information schema, which displays standard info in 7.4 - support elsewhere is variable I believe. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] newbie question... how do I get table structure?
Franco Bruno Borghesi [EMAIL PROTECTED] writes: Does anyone know if the ansi sql standard defines any way to do this? I've seen the DESCRIBE TABLE/INDEX/... or SHOW TABLE/INDEX/... commands in other databases, but I don't really know if they are extensions or not. They are extensions (and very nonstandard ones at that). What the SQL standard provides are standardized views of the system catalogs located in the INFORMATION_SCHEMA schema. The per-spec way to do this would be something like select column_name, data_type from information_schema.columns where table_name = 'foo' order by ordinal_position; Now Postgres only got around to supporting the INFORMATION_SCHEMA views in 7.4 (although in principle you could have defined most of these views earlier, certainly in 7.3). I'm not real sure how many other DBs support INFORMATION_SCHEMA either ... it may not be all that standard. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] newbie question on database structure
On 15. jan 2004, at 18:51, JustSomeGuy wrote:
I want to design a data base that can hold the following structure
struct {
unsigned short a;
unsigned short b;
unsigned int len;
unsigned char *data;
} myObject;
a and b describe the type of object, len is the number of bytes that
are required to hold the object.
and of course data points to the object
Of course the size of the objects is not a constant so this is where I
get confused...
How do I define a database to hold these in postgres?
You want a data-type which can hold variable amount of data.
create table myObject (
a integer,
b integer,
len integer,
data bytea
);
Now, I assume that your unsigned short is a 2-byte integer. However
postgresql doesn't have an unsigned short (afaik), but it has a signed
version. Unless you're going to be storing *a lot* of these records,
you'll probably be fine with just using a 4-byte integer. You might of
course consider dropping the unsigned, or doing your own conversion
(to/from signed).
The bytea (byte-array) type will store up to 1 or 2 gigabyte of data
(never remember which one). Only caveat is that when you are inserting
you may have to escape the null character, backslash and single-quote.
See the docs for how to
(http://www.postgresql.org/docs/current/static/datatype-binary.html).
If you are using libpq (the C interface), it has functions that will
help you... check the docs for libpq for that too.
If you are actually going to store multi-megabyte data buffers in
there, there are considerations of memory allocation (which may get
pretty extreme if you try to transfer huge buffers at once). Consider
using the Large Object interface instead.
Hope this helps.
David Helgason
Over the Edge Entertainments
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] newbie question on database structure
I want to design a data base that can hold the following structure
struct {
unsigned short a;
unsigned short b;
unsigned int len;
unsigned char *data;
} myObject;
a and b describe the type of object, len is the number of bytes that
are required to hold the object.
and of course data points to the object
Of course the size of the objects is not a constant so this is where I
get confused...
How do I define a database to hold these in postgres?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Newbie-question
Victor Spång Arthursson wrote: Are presently converting from mysql to postgresql, and my first newbiequestion is how to make all the rows in a result from a select just swosh by? That is, I dont want to see them page for page; just to scroll by so I can se the last line with the number of corresponding rows. You seem to want to see the number of corresponding rows, not the stuff swish by - or at least, I would hope you are more interested in the count and not just text flying by... In that case, do a select count(*) from SQL select... Andrew Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Newbie-question
Are presently converting from mysql to postgresql, and my first newbiequestion is how to make all the rows in a result from a select just swosh by? That is, I dont want to see them page for page; just to scroll by so I can se the last line with the number of corresponding rows. And is there a way to see how long time a query took to execute without running a EXPLAIN ANALYSE on the query? Sincerely Victor - Copenhagen/Malmoe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Newbie-question
2003-10-28 kl. 14.33 skrev Jeff: 1. in psql, \pset pager will turn paging off. Although, if you really want a row count, a much better way to do that is select count(*) from [rest of select statement] Thanks. I'm not new to SQL, just to postgresql, so I know about the Count-function ;) Reason for the question is that I want to know the number of corresponding rows cause I'm in a developing phase and due to needs to validate the result the number of rows are important. in psql, use \timing and it will print how long each query you type in took. Perfekt! Next question: what is the command in postresql that matches the DESCRIBE-command in mysql? That's, to get the fieldnames and additional info about them Best regards, Victor ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Newbie-question
On Wed, Oct 29, 2003 at 01:24:56AM +1100, Brendan Jurd wrote: from your question that you are), then the results are reported back via the utility less (or a less-like program internal to psql, can someone else clarify?) Actually, it's your $PAGER environment variable (and there's the usual UNIX-y ways of handling it if $PAGER is not set). So you can make it anything you want. In 7.3.x and later, you can also do \pset pager to turn off the pager in psql. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] !! Newbie question!!!! connecting to multiple databases
On Fri, Aug 24, 2001 at 09:53:00PM +, uncleleo wrote:
I am attemping to create multiple databases with Postgresql ver. 7.0.3
running on Mandrake 8.0 rpm. The tool that I am using is Pgadmin ver 7.1.0.
Can someone tell me how I can connect to different databases in a single
select statement? Such as, I have a database named Customer and another
named Products. I wish to Select from table A in the Customer database
and table A in Products database. I know that its possible in SQL Server and
other databases.
If anyone can I help I would appreciate it.
postgresql doesn't allow you to connect to anything but tables
in the 'current' database via sql.
but in an external language you can have several connections
open, each to a different database:
#!perl
use DBI;
my $db1 = DBI-connect('dbi:Pg:dbname=people');
my $db2 = DBI-connect('dbi:Pg:dbname=inventory');
my $st1 = $db1-prepare('select * from client');
$st1-execute();
while ( my $rec = $st1-fetchrow_hashref ) {
my $st2 = $db2-prepare(select $rec-{afield} from $rec-{atable});
$st2-execute();
foreach my $item ( $st2-fetchrow_hashref ) {
...
}
}
but it may be a sign that you need to revisit your data
paradigm, instead... (there are some cases where three levels of
structure are handy: db-table-field -- but usually two does
quite nicely: table-field within db.)
--
Hey, let's change the whole justice system. Everybody gets to
kill one person -- if you do two, you go to jail. That should
cut down on the abrasive personalities, don't you think?
[EMAIL PROTECTED]
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] !! Newbie question!!!! connecting to multiple databases
On Fri, 24 Aug 2001, uncleleo wrote: I am attemping to create multiple databases with Postgresql ver. 7.0.3 running on Mandrake 8.0 rpm. The tool that I am using is Pgadmin ver 7.1.0. Can someone tell me how I can connect to different databases in a single select statement? Such as, I have a database named Customer and another named Products. I wish to Select from table A in the Customer database and table A in Products database. I know that its possible in SQL Server and other databases. You currently don't... See archives for lots of discussion of this recently (mostly part of the mysql/pgsql thread I think). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Newbie Question
Hallo!
How do I create an autoincrement field in a postgresql table???
What are the correct field type and parameters
Well, what about using sequences?
create sequence tralalala;
create table huibui
(
id integer primary key default nextval('tralalala'),
field1 references table1,
and-so-on references all-other-tables
);
How, each time You insert some data in huibui using
insert into huibui (field1, and-son-on) values (?, ..);
the 'tralala' counter will be increased by one (nextval).
Saluti!
Ludwig
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Newbie Question
Clay Judi Kinney writes: How do I create an autoincrement field in a postgresql table??? See FAQ. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] newbie question -- oid
Cefull Lo writes: What is the upper limit of OID? 2^31-1 -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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: [GENERAL] newbie question - INSERT
On Tue, 10 Apr 2001, Cefull Lo wrote:
When I type INSERT INTO friend
VALUES ('', '', '');
it returns
INSERT 19748 1
what means of 19748 and 1?
19748 is the OID of that record, and 1 means one record was inserted.
For information on OID's, look in the documentation.
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
---
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] newbie question - INSERT
When I type INSERT INTO friend
VALUES ('', '', '');
it returns
INSERT 19748 1
what means of 19748 and 1?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Newbie question :-)
On Sat, 24 Feb 2001, Bela Lantos wrote: I just started learning Database Design, our programs have to work on Oracle, but at home I run Linux. Can any of you tell me how portable are the programs written for Progresql? Would they run on Oracle without problem? That all depends on the programming interface you are writing your programs with. If you are using Oracle's or PostgreSQL's native libraries, they are not going to be portable at all. However, if you are building your code with a data abstraction layer (i.e., Java's JDBC, Perl's DBI, ODBC), you will have a lot more portability between database systems. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Please come home with me ... I have Tylenol!!
[GENERAL] newbie question
hi all, Is it possible to store java Objects in postgres and invoke methods on them??. Is it possible to store XML in postgres?. please reply ASAP thanx in adv. prashanth __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
Re: [GENERAL] newbie question
prashanth bhat [EMAIL PROTECTED] writes: hi all, Is it possible to store java Objects in postgres and invoke methods on them??. Is it possible to store XML in postgres?. The answer to both your questions is "yes, but..." Any Java object that implements Serializable can be serialized into a byte stream, which can them be stored as a BLOB in Postgres. You can't invoke methods on it in that form; you have to read it back in and de-serialize it into a real object. XML is text, and can be stored in Postgres like any other chunk of text. To do anything with it, though, you'll need to read it back out and parse it using SAX or another XML parser. Hope this helps... -Doug
Re: [GENERAL] Newbie question
what do u mean by the datadir problem.. if pg_ctl asks for a datadir then ucan set it in /etc/profile and start again.. Hope this helps Anand On Mon, Jan 08, 2001 at 03:44:34PM +0100, MUMCU, Burak wrote: Hi all, I'm using Suse Linux 7.0 and I have installed PostgreSQL. The problem is when I start Postgres with pg_ctl I have always the 'datadir' problem. How can I solve this problem ? Regards. Burak MUMCU MARSA Kraft Jacobs Suchard Bilgi Sistemleri Mdrlg UYGULAMA GELISTIRME UZMANI * 00 90 216 325 57 82 / 332 L 00 90 216 325 57 90 GSM 00 90 533 336 19 57
Re: [GENERAL] newbie question:
ALTER USER postgres WITH PASSWORD '[enter password]' http://www.postgresql.org/users-lounge/docs/7.0/user/sql-alteruser.htm - Original Message - From: "Leon van Dongen" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 18, 2000 1:56 AM Subject: [GENERAL] newbie question: I have been expirementing with PostgreSQL but now I have compiled PHP with PostgreSQL I need an superuser to call the database from my scripts. Postgres is already defined as the superuser but how can I set the superuser password ? Is there a admin script just like MySQL or should I insert it into the pg_??(group,user) table on the default database ? please let me know Leon
[GENERAL] Newbie Question
Is there an upper limit on the size of a "text" character field? If so, how can I extend it? Thanks, John begin:vcard adr;dom:;;232 E. Lyons;Spokane;WA;99208; n:Pilley;John x-mozilla-html:FALSE org:Settlement Plus, Inc. version:2.1 email;internet:[EMAIL PROTECTED] title:Software Engineer tel;fax:509-484-7265 tel;work:509-484-7165 x-mozilla-cpt:;0 fn:John Pilley end:vcard
RE: [GENERAL] Newbie Question
John- According to the documentation and Bruce M's book, there is no limit. I've never hit a limit while putting some pretty large (three page) narratives in a text field. Practically speaking, I would guess that one will take a performance hit due to fragmentation when storing big chunks of data comingled with smaller chunks. -Nick Fankhauser -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Pilley Sent: Wednesday, November 01, 2000 12:10 PM To: [EMAIL PROTECTED] Subject: [GENERAL] Newbie Question Is there an upper limit on the size of a "text" character field? If so, how can I extend it? Thanks, John
Re: [GENERAL] Newbie Question
On Wed, Nov 01, 2000 at 12:32:58PM -0500, Nick Fankhauser wrote: According to the documentation and Bruce M's book, there is no limit. I've never hit a limit while putting some pretty large (three page) narratives in a text field. There is no limit on the "text" type as such, but there is still the limit on the total size of a row: about 8k by default, 32k if you're willing to recompile (see the FAQ). This limit will be removed entirely by TOAST in 7.1 . Richard
Re: [GENERAL] newbie question: ERROR: getattproperties: no attribute tuple 1259 -2
Isaac and Tom, Awesome, thanx. I thought i was doing something wrong. I suggest that some sort of warning about this go on the pgsql website (and other places where us PPC types will notice it) so no one else has to beat their head on the wall over this. Postgres 7.0.* doesn't work on PPC unless compiled -O0. The above is a typical symptom of being compiled with higher optimization settings. Unfortunately, it seems that our PPC RPMs for 7.0.2 were compiled with the wrong -O level :-(. (Don't ask me why the RPMs ended up that way when a clean source compilation uses -O0, but there it is.) Why is this? We're running 7.0.2 and have not performed any of these compile-time gymnastics...and we're not having any problems (although we've barely scratched the surface of what PostgreSQL can do). We're running Yellow Dog Linux Champion Server 1.2 on a 450Mhz PowerPC G4. By the way- does anybody know of any resources related to securing databases. We'd like to only allow certain users access to each database, but cannot find the appropriate section in any of the documentation pages. Thank you for your time and assistance. Sincerely, Robert Vogt IV CEO ArborHost
Re: [GENERAL] newbie question: ERROR: getattproperties: no attribute tuple 1259 -2
* Robert Vogt IV [EMAIL PROTECTED] [001028 12:10]: [SNIP] By the way- does anybody know of any resources related to securing databases. We'd like to only allow certain users access to each database, but cannot find the appropriate section in any of the documentation pages. Look at pg_hba.conf in your data directory. Here is mine from 7.0.2: # cat pg_hba.conf # # Example PostgreSQL host access control file. # # # This file controls what hosts are allowed to connect to what # databases # and specifies some options on how users on a particular host are # identified. # It is read each time a host tries to make a connection to a # database. # # Each line (terminated by a newline character) is a record. A record # cannot # be continued across two lines. # # There are 3 kinds of records: # # 1) comment: Starts with #. # # 2) empty: Contains nothing excepting spaces and tabs. # # 3) content: anything else. # # Unless specified otherwise, "record" from here on means a content # record. # # A record consists of tokens separated by spaces or tabs. Spaces and # tabs at the beginning and end of a record are ignored as are extra # spaces and tabs between two tokens. # # The first token in a record is the record type. The interpretation # of the # rest of the record depends on the record type. # # Record type "host" # -- # # This record identifies a set of network hosts that are permitted to # connect # to databases. No network hosts are permitted to connect except as # specified # by a "host" record. See the record type "local" to specify # permitted # connections using UNIX sockets. # # Format: # # host DBNAME IP_ADDRESS ADDRESS_MASK USERAUTH [AUTH_ARGUMENT] # # DBNAME is the name of a PostgreSQL database, "all" to indicate all # databases, or "sameuser" to restrict a user's access to a database # with the same user name. # # IP_ADDRESS and ADDRESS_MASK are a standard dotted decimal IP address # and # mask to identify a set of hosts. These hosts are allowed to connect # to # Database DBNAME. # # USERAUTH is a keyword indicating the method used to authenticate the # user, i.e. to determine that the principal is authorized to connect # under the PostgreSQL username he supplies in his connection # parameters. # # ident: Authentication is done by the ident server on the remote # host, via the ident (RFC 1413) protocol. AUTH_ARGUMENT, # if # specified, is a map name to be found in the pg_ident.conf # file. # That table maps from ident usernames to PostgreSQL # usernames. The # special map name "sameuser" indicates an implied map (not # found # in pg_ident.conf) that maps every ident username to the # identical # PostgreSQL username. # # trust: No authentication is done. Trust that the user has the # authority to use whatever username he specifies. Before # PostgreSQL version 6, all authentication was done this # way. # # reject: Reject the connection. # # password: Authentication is done by matching a password supplied # in clear # by the host. If AUTH_ARGUMENT is specified then the # password # is compared with the user's entry in that file (in the # $PGDATA # directory). See pg_passwd(1). If it is omitted then # the # password is compared with the user's entry in the # pg_shadow # table. # # crypt: Authentication is done by matching an encrypted password # supplied # by the host with that held for the user in the pg_shadow # table. # # krb4: Kerberos V4 authentication is used. # # krb5: Kerberos V5 authentication is used. # Record type "hostssl" # - # # This record identifies the authentication to use when connecting to # a # particular database via TCP/IP sockets over SSL. Note that normal # "host" records are also matched - "hostssl" records can be used to # require a SSL connection. # This keyword is only available if the server is compiled with SSL # support # enabled. # # The format of this record is identical to that of "host". # Record type "local" # -- # # This record identifies the authentication to use when connecting to # a # particular database via a local UNIX socket. # # Format: # # local DBNAME USERAUTH [AUTH_ARGUMENT] # # The format is the same as that of the "host" record type except that # the # IP_ADDRESS and ADDRESS_MASK are omitted and the "ident", "krb4" and # "krb5" # values of USERAUTH are not allowed. # For backwards compatibility, PostgreSQL also accepts pre-version 6 # records, # which look like: # # all 127.0.0.10.0.0.0 # TYPE DATABASEIP_ADDRESSMASK USERAUTH MAP #host all 127.0.0.1 255.255.255.255 trust # The
Re: [GENERAL] newbie question: ERROR: getattproperties: no attribute tuple 1259 -2
"Robert Vogt IV" [EMAIL PROTECTED] writes: Postgres 7.0.* doesn't work on PPC unless compiled -O0. The above is a typical symptom of being compiled with higher optimization settings. Unfortunately, it seems that our PPC RPMs for 7.0.2 were compiled with the wrong -O level :-(. (Don't ask me why the RPMs ended up that way when a clean source compilation uses -O0, but there it is.) Why is this? We're running 7.0.2 and have not performed any of these compile-time gymnastics...and we're not having any problems Did you compile from source? If so I'd expect things to be fine. It's just the PPC RPMs that are (or, shortly, were) on our FTP server that are no good. regards, tom lane
Re: [GENERAL] newbie question: ERROR: getattproperties: no attribute tuple 1259 -2
Mr. Lane, Why is this? We're running 7.0.2 and have not performed any of these compile-time gymnastics...and we're not having any problems Did you compile from source? If so I'd expect things to be fine. It's just the PPC RPMs that are (or, shortly, were) on our FTP server that are no good. Oh..ok. Yes, we did compile from source. Right now, I still trying to figure out access permissions... Are there any examples of people using database authentication? Can usernames conflict between the general postgresql username pool and the by-database authentication files? Thank you for your assistance. Sincerely, Robert Vogt IV CEO ArborHost
Re: [GENERAL] newbie question: ERROR: getattproperties: noattribute tuple 1259 -2
Awesome, thanx. I thought i was doing something wrong. I suggest that some sort of warning about this go on the pgsql website (and other places where us PPC types will notice it) so no one else has to beat their head on the wall over this. AP2P --i on 10/27/00 10:52 PM, Tom Lane at [EMAIL PROTECTED] wrote: Isaac [EMAIL PROTECTED] writes: ERROR: getattproperties: no attribute tuple 1259 -2 Postgres 7.0.* doesn't work on PPC unless compiled -O0. The above is a typical symptom of being compiled with higher optimization settings. Unfortunately, it seems that our PPC RPMs for 7.0.2 were compiled with the wrong -O level :-(. (Don't ask me why the RPMs ended up that way when a clean source compilation uses -O0, but there it is.)
[GENERAL] newbie question
is it possible to pull a column from a postgres table then have it linked via html and php to the corresponding row. If so how would that be accomplished?
[GENERAL] Newbie question: Does PostgreSQL have stored procedures?
I saw that you can have functions written in C or SQL, but can you write a precompiled SQL statement (such as SELECT * FROM tablename WHERE ...) that returns a set of rows? I couldn't find anything that addresses this in the documentation (does that mean it can't be done yet?). Andrew Hopper, Research Engineer BITC, Georgia Institute of Technology http://bitc.gatech.edu/ahopper
