Re: [SQL] date_trunc for 5 minutes intervals
On Sunday 19 October 2003 14:38, email lists wrote: > Hi All, > > I am wanting to perform the equivalent of date_trunc to 5/10/15 minute > intervals. As this does mnot seem to be natively supported by > date_trunc, can anyone point me in the right direction to possible write > the SQL ro acheive the desired outcome? I'd probably convert to seconds-since-epoch and then subtract that value modulo 300 and convert back. Might be worth checking the cookbook link on http://techdocs.postgresql.org/ since I can't believe you are the first person to need this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] query or design question
Hi, I have a table like so CREATE TABLE imagene ( id int bioassay_idint gene_idtext s_row int s_column int s_meta_row int s_meta_column int sig_median numeric bkg_median numeric ); Rows are unique on (bioassay_id, gene_id, s_row, s_column, s_meta_row, s_meta_column) They are grouped like this (note, the counts will not always be the same) cabbage=# select bioassay_id, count(*) from imagene group by bioassay_id; bioassay_id | count -+--- 106 | 10944 107 | 10944 And I need to generate an output like this.. bioassay_id=106bioassay_id=107 -- -- gene_id, sig_median, bkg_median, sig_median, bkg_median I can do something like this SELECT a.gene_id, a.sig_median, a.bkg_median, b.sig_median, b.bkg_median FROM imagene a, imagene b WHERE a.s_meta_row = b.s_meta_row AND a.s_meta_col = b.s_meta_col AND a.s_row = b.s_row AND a.s_column = b.s_column AND a.bioassay_id = 106 AND b.bioassay_id = 107; But this is quite slow... And not generic as I may need to generate the output for more than two bioassay_ids. Also I may need to do an outer join to make sure I get all rows from both data sets? I am not sure if a crosstab function will do this? Any ideas of pointers would be greatly appreciated Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Max input parameter for a function
After a long battle with technology,[EMAIL PROTECTED] ("Kumar"), an earthling, wrote:
> While trying to allocate about 36 input parameters, I got an error saying that the
> max input parameter for a function is only 32.
>
> Is it right? How to overcome this? Because I wanna insert records into a table of 55
> columns with a lot of NULL able columns.
>
> I am using Postgres 7.3.4 on RH Linux 7.2.
>
> Note: I can't use 2 functions , one to insert 32 rows into the table
> first and the update the remaining columns with other
> function. Because there are only 27 columns that are not null.
The default compiled in is to limit the number of parameters to 32.
You can recompile it and modify the number of input parameters.
Somehow, the notion of a table with 55 columns strikes me as a
situation where normalization is likely to be BADLY needed...
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/linux.html
Rules of the Evil Overlord #156. "If I have the hero and his party
trapped, I will not wait until my Superweapon charges to finish them
off if more conventional means are available."
[SQL] Unable to user pg_restore
Dear Friends, I have created a compressed backup for the database - sampledb using the following $ pg_dump -h 192.128.2.51 -v -u -f /home/db_repository/sampledb20031020.sql.tar.gz wats -F c while I try to restore this with the pg_restore function as follows $ pg_restore -d sampledbtest -f /home/db_repositorysampledb.sql.tar.gz -F c -v -c -O -h 192.128.2.51 -p 5432 -uUser name: postgres It never prompt for password, so I just typed the password , and get the error as follows, postgrespg_restore: [archiver] did not find magic string in file header$ Anyone could advise me on this pls. Kumar
[SQL] Question regarding triggers
I'm writing a trigger but running into problems. My problem is that I can not determine anyway to tell what fields are in the *new* record. Without knowing what fields are there I get runtime errors if that's not what is being updated example: CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' begin If new.shipment_type_id = 4 then --do something end if; return new; end; in the above example everything works if shipment_type_id is being updated. If it is not I will receive a run-time error because shipment_type_id is not part of the record new. Please help, Dmitri Do you Yahoo!? The New Yahoo! Shopping - with improved product search
[SQL] Scripting only the functions
Dear Friends, I am working with Postgres 7.3.4 on RH Linux server 7.2. Using pg_dump I could manage to take a script for all the DB objects. But wanted to take the script (DDL) for all the scripts in my database. While I searched I dont find any options in the pg_dump except for script tables only. Is there a way? Thanks Kumar
Re: [SQL] Question regarding triggers
Why dont you try to write your trigger in C? On Mon, 20 Oct 2003, Dmitri Fuerle wrote: > >I'm writing a trigger but running into problems. My problem is that I can not > determine anyway to tell what fields are in the *new* record. Without knowing what > fields are there I get runtime errors if that's not what is being updated example: > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > begin > If new.shipment_type_id = 4 then > --do something > end if; > return new; > end; > > in the above example everything works if shipment_type_id is being updated. If it > is not I will receive a run-time error because shipment_type_id is not part of the > record new. > > Please help, > Dmitri > > > > > - > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Question regarding triggers
> Why dont you try to write your trigger in C? Hi, one cannot write triggered procedures in C. Currently, it can only be written in plpsgql. > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > > begin > > If new.shipment_type_id = 4 then > > --do something > > end if; > > return new; > > end; > > in the above example everything works if shipment_type_id is being > > updated. If it is not I will receive a run-time error because > > shipment_type_id is not part of the record new. All the fields of updated record must available in NEW variable. Refer the manual for details. Forward your sql & run-time error for further assistance. regards, bhuvaneswaran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [postgres] PostgreSQL-DB auf Web-Schnittstelle bringen
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - -- [EMAIL PROTECTED] wrote: > Welches Verfahren/Vorgehen würdet Ihr mir denn empfehlen, wenn ich eine > PostgreSQL- Datenbank auf eine Web-Schnittstelle bringen will? was meinst Du denn damit genau? Du hast irgendeine Applikation die ihre Daten in Postgres ablegt und nun soll das auch im Web angezeigt werden? Oder Du suchst ein Web-basiertes Admin-Tool? Für ersteres eignet sich aufgrund der komfortaben Formularbehandlung prima Embperl (http://www.ecos.de/embperl/), zusammen mit DBI wenn man SQL selbst baut; es gibt auch Perl-Module, die einem das SQL abnehmen ... Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ ** Berufsverbot: http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM-Server sucht Stecker fuer TCP/IP und Strom; siehe http://odem.org ** ODEM-Tour: http://tour.odem.org/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/k65GOndlH63J86wRAiYkAKC56gcoBfOPcL00qsEtRrXzwrMwXwCgvHy1 bm3RFxT2iv1i7sjRXA8kZ+4= =GeKG -END PGP SIGNATURE- Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Question regarding triggers
On Mon, 20 Oct 2003, A.Bhuvaneswaran wrote: > > Why dont you try to write your trigger in C? > > Hi, one cannot write triggered procedures in C. Currently, it can only be > written in plpsgql. Where did you get that impression from? Do an SELECT tgrelid,tgfoid,proname from pg_trigger,pg_proc where tgfoid=pg_proc.oid and prolang=13; in your system to check if you have any :) > > > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > > > begin > > > If new.shipment_type_id = 4 then > > > --do something > > > end if; > > > return new; > > > end; > > > in the above example everything works if shipment_type_id is being > > > updated. If it is not I will receive a run-time error because > > > shipment_type_id is not part of the record new. > > All the fields of updated record must available in NEW variable. Refer > the manual for details. Forward your sql & run-time error for further > assistance. > > regards, > bhuvaneswaran > > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Question regarding triggers
"A.Bhuvaneswaran" <[EMAIL PROTECTED]> writes: >> Why dont you try to write your trigger in C? > Hi, one cannot write triggered procedures in C. Oh? All the built-in trigger procedures are. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question regarding triggers
Thanks for the help. I discovered my error while coming up with a better example. Thanks, Dmitri"A.Bhuvaneswaran" <[EMAIL PROTECTED]> wrote: > Why dont you try to write your trigger in C?Hi, one cannot write triggered procedures in C. Currently, it can only bewritten in plpsgql.> > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS'> > begin> > If new.shipment_type_id = 4 then> > --do something> > end if;> > return new;> > end;> > in the above example everything works if shipment_type_id is being> > updated. If it is not I will receive a run-time error because> > shipment_type_id is not part of the record new.All the fields of updated record must available in NEW variable. Referthe manual for details. Forward your sql & run-time error for further assistance.regards,bhuvaneswaran Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [SQL] [postgres] PostgreSQL-DB auf Web-Schnittstelle bringen
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hallo, - -- Ian Barwick <[EMAIL PROTECTED]> wrote: > Fuer diese Aufgabe wuerde sich eignen z.B. phpPgAdmin: > http://phppgadmin.sourceforge.net/ vor einiger Zeit hatte ich mir das mal angeschaut -- und fand das nur grauselig. OK, besser als nichts. Aber komfortabel war das in meinen Augen nicht. i.d.R. baue ich meine Datenbanken von Hand in einem kleinen Installationsprogramm auf; die paar SQL-Befehle sind ja auch nicht so schwer und dafür lässt sich das dann einfach auf dem endgültigen Server einrichten. Will mir nun aber auch mal Pgadmin 3 anschauen, das sieht zumindes in der Beschreibung gut aus! Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ ** Berufsverbot: http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM-Server sucht Stecker fuer TCP/IP und Strom; siehe http://odem.org ** ODEM-Tour: http://tour.odem.org/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/k67hOndlH63J86wRAgJsAJ0byIgTrmvk4ZJjM07us34zLRTXZwCdHFtm pW/0TszYeuAd8sgQO27Bdfc= =iu2e -END PGP SIGNATURE- Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Which is faster SQL or PL/PGSQL
On Sun, 19 Oct 2003, Christopher Browne wrote:
> The world rejoiced as [EMAIL PROTECTED] ("George A.J") wrote:
> > i am converting an MSSQL database to Postgres. there is a lot of
> > procedures to convert.
> >
> > which language is best for functions, SQL or plpgsql.
> >
> > which is faster . i am using postgres 7.3.2
>
> Hmm? This doesn't seem to make much more sense than the question of
> what colour a database should be ("Mauve has more RAM...").
Transact-SQL stored procedures pseudo-compile their execution plans the
first time they are run, which can result in faster subsequent executions.
I'm guessing the poster was wondering if plpgsql functions offered similar
performance benefits vs. equivalent SQL.
michael
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Which is faster SQL or PL/PGSQL
On Monday 20 October 2003 16:36, Michael Pohl wrote:
> On Sun, 19 Oct 2003, Christopher Browne wrote:
> > The world rejoiced as [EMAIL PROTECTED] ("George A.J") wrote:
> > > i am converting an MSSQL database to Postgres. there is a lot of
> > > procedures to convert.
> > >
> > > which language is best for functions, SQL or plpgsql.
> > >
> > > which is faster . i am using postgres 7.3.2
> >
> > Hmm? This doesn't seem to make much more sense than the question of
> > what colour a database should be ("Mauve has more RAM...").
>
> Transact-SQL stored procedures pseudo-compile their execution plans the
> first time they are run, which can result in faster subsequent executions.
> I'm guessing the poster was wondering if plpgsql functions offered similar
> performance benefits vs. equivalent SQL.
To which the answer would have to be "yes and no".
A plpgsql function is compiled on its first call, so any queries will have
their plans built then, and you will gain on subsequent calls.
However, since the plan will have to be built without knowledge of the actual
values involved, you might not get as good a plan as you could with the
actual values. For example:
SELECT * FROM uk_towns WHERE town_name LIKE 'T%';
SELECT * FROM uk_towns WHERE town_name LIKE 'X%';
There's a good chance a seq-scan of the table is the best plan for the first
query, but if you have an index on town_name you probably want to use it in
the second case.
So - gain by not re-planning on every call, but maybe lose because your plan
is not so precise.
Of course, any queries you build dynamically and run via EXECUTE will have to
be planned each time.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] query or design question
On Monday 20 October 2003 11:58, Adam Witney wrote: > Hi, > > I have a table like so > > CREATE TABLE imagene ( > id int > bioassay_idint > gene_idtext > s_row int > s_column int > s_meta_row int > s_meta_column int > sig_median numeric > bkg_median numeric > ); > And I need to generate an output like this.. > > bioassay_id=106bioassay_id=107 > -- -- > gene_id, sig_median, bkg_median, sig_median, bkg_median > I am not sure if a crosstab function will do this? There are some examples in contrib/tablefunc that might well be useful for you. You should also check out the "Set Returning Functions" article on http://techdocs.postgresql.org/ If you find your requirements getting particularly complex, I'd be tempted to do the hard work in the application. -- Richard Huxton Archonet Ltd ---(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] Unable to user pg_restore
"Kumar" <[EMAIL PROTECTED]> writes: > $ pg_restore -d sampledbtest -f /home/db_repositorysampledb.sql.tar.gz -F c= > -v -c -O -h 192.128.2.51 -p 5432 -u > User name: postgres This is the wrong way to invoke pg_restore. "-f file" is an *output* file name. -d and -f are mutually contradictory options ... not sure why the thing doesn't complain about that. What you want is just pg_restore -d sampledbtest -v -c -O -h 192.128.2.51 -p 5432 -u inputfilename Depending on whether -d or -f takes precedence, the program might have tried to overwrite your dump file; better check. regards, tom lane ---(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] Which is faster SQL or PL/PGSQL
Richard Huxton wrote: So - gain by not re-planning on every call, but maybe lose because your plan is not so precise. Of course, any queries you build dynamically and run via EXECUTE will have to be planned each time. This question gets even more complex in 7.4, where many simple SQL functions will get inlined, and library preloading is available to speed that first PL/pgSQL call. I think the best way to answer this question for any particular function is to try it both ways (if the function *can* be written as a SQL function) and see for yourself. And if you do this in 7.3, redo it when you upgrade to 7.4. HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Scripting only the functions
On Monday 20 October 2003 14:04, Kumar wrote:
> Dear Friends,
>
> I am working with Postgres 7.3.4 on RH Linux server 7.2.
>
> Using pg_dump I could manage to take a script for all the DB objects. But
> wanted to take the script (DDL) for all the scripts in my database. While I
> searched I dont find any options in the pg_dump except for script tables
> only.
That'll be because there isn't one (as far as I know).
You could pipe the output of your pg_dump schema through a small perl script
something like:
#!/usr/bin/perl -w
use strict;
my ($line, $in_func);
while ($line = <>) {
if ($line =~ /^CREATE.+FUNCTION/) { $in_func = 1; }
if ($in_func) { print $line; }
if ($line =~ /^\s+LANGUAGE\s+\w+;$/) {
$in_func = 0;
print "\n";
}
}
That should do it, although it will fail if you have a line in your function
starting with "LANGUAGE" (doesn't seem likely to me).
The other options you could use are:
1. To write your own function-dumping script (use psql -E then \df+ to see how
to get the function source).
2. Use pgadmin if there aren't too many functions to process, and do it by
hand.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Scripting only the functions
Kumar, > Using pg_dump I could manage to take a script for all the DB objects. But > wanted to take the script (DDL) for all the scripts in my database. While I > searched I dont find any options in the pg_dump except for script tables > only. > > Is there a way? Currently, no. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Which is faster SQL or PL/PGSQL
On Monday 20 October 2003 18:24, Joe Conway wrote: > Richard Huxton wrote: > > So - gain by not re-planning on every call, but maybe lose because your > > plan is not so precise. > > > > Of course, any queries you build dynamically and run via EXECUTE will > > have to be planned each time. > > This question gets even more complex in 7.4, where many simple SQL > functions will get inlined, and library preloading is available to speed > that first PL/pgSQL call. What will be the effects of inlining? Does it mean the planner merges the function's plan into the larger query? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Which is faster SQL or PL/PGSQL
Richard Huxton wrote: On Monday 20 October 2003 18:24, Joe Conway wrote: This question gets even more complex in 7.4, where many simple SQL functions will get inlined, and library preloading is available to speed that first PL/pgSQL call. What will be the effects of inlining? Does it mean the planner merges the function's plan into the larger query? Yes, I believe so (well, actually the optimizer). An inlined SQL function ends up behaving like a macro that expands at run time and is therefore quite fast -- no function call overhead at all. Here is the comment from the source (src/backend/optimizer/util/clauses.c): /* * inline_function: try to expand a function call inline * * If the function is a sufficiently simple SQL-language function * (just "SELECT expression"), then we can inline it and avoid the * rather high per-call overhead of SQL functions. Furthermore, this * can expose opportunities for constant-folding within the function * expression. * * We have to beware of some special cases however. A directly or * indirectly recursive function would cause us to recurse forever, * so we keep track of which functions we are already expanding and * do not re-expand them. Also, if a parameter is used more than once * in the SQL-function body, we require it not to contain any volatile * functions (volatiles might deliver inconsistent answers) nor to be * unreasonably expensive to evaluate. The expensiveness check not only * prevents us from doing multiple evaluations of an expensive parameter * at runtime, but is a safety value to limit growth of an expression * due to repeated inlining. * * We must also beware of changing the volatility or strictness status * of functions by inlining them. * * Returns a simplified expression if successful, or NULL if cannot * simplify the function. */ Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Which is faster SQL or PL/PGSQL
Joe, > Yes, I believe so (well, actually the optimizer). An inlined SQL > function ends up behaving like a macro that expands at run time and is > therefore quite fast -- no function call overhead at all. ... but only in 7.4. In 7.2 and I think in 7.3 this was not implemented. While we're on the topic, anyone know any good ways to speed up EXECUTE statements in PL/pgSQL functions? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Crosstab question
Hi,
I am trying to figure out the use of crosstab(text sql, int N)
The sql I have is
cabbage=# select geneid, bioassay_id, sig_median from imagene order by 1,2;
geneid | bioassay_id | sig_median
-+-+
16s rRNA (AP1A1)| 107 |65535.0
16s rRNA (AP1A1)| 108 | 1904.0
16s rRNA (AP1A1)| 109 |65535.0
16s rRNA (AP2A1)| 106 | 197.0
16s rRNA (AP2A1)| 108 | 197.0
16s rRNA (AP2A1)| 109 |10525.0
16s rRNA (MWG1B1) | 106 | 49.0
16s rRNA (MWG1B1) | 107 | 282.0
16s rRNA (MWG1B1) | 108 | 49.0
16s rRNA (MWG1B1) | 109 | 282.0
However when I use a crosstab function like so
cabbage=# select * from crosstab('select geneid, bioassay_id, sig_median
from imagene order by 1,2;', 4) as ct(geneid text, b106 numeric, b107
numeric, b108 numeric, b109 numeric);
geneid | b106 | b107 | b108 | b109
-+-+-+-+-
16s rRNA (AP1A1)| 65535.0 | 1904.0 | 65535.0 |
16s rRNA (AP2A1)| 197.0 | 197.0 | 10525.0 |
16s rRNA (MWG1B1) |49.0 | 282.0 |49.0 | 282.0
However it should be:
geneid | b106 | b107 | b108 | b109
-+-+-+-+-
16s rRNA (AP1A1)| | 65535.0 | 1904.0 | 65535.0
16s rRNA (AP2A1)| 197.0 | | 197.0 | 10525.0
16s rRNA (MWG1B1) |49.0 | 282.0 |49.0 | 282.0
The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.
Am I using this function correctly? What is supposed to happen with missing
values?
Thanks for any help
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Which is faster SQL or PL/PGSQL
Josh Berkus wrote: Yes, I believe so (well, actually the optimizer). An inlined SQL function ends up behaving like a macro that expands at run time and is therefore quite fast -- no function call overhead at all. ... but only in 7.4. In 7.2 and I think in 7.3 this was not implemented. Yeah, that's what my original post said ;-) While we're on the topic, anyone know any good ways to speed up EXECUTE statements in PL/pgSQL functions? Never tried it, but is it possible to use a prepared statement inside a PL/pgSQL function? In any case, you can in other PLs. And with library preloading (starting in 7.4), the first call to other PLs is similar to that of PL/pgSQL. See: http://archives.postgresql.org/pgsql-patches/2003-07/msg00239.php Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Which is faster SQL or PL/PGSQL
Joe, > Never tried it, but is it possible to use a prepared statement inside a > PL/pgSQL function? In any case, you can in other PLs. And with library > preloading (starting in 7.4), the first call to other PLs is similar to > that of PL/pgSQL. See: >http://archives.postgresql.org/pgsql-patches/2003-07/msg00239.php Not sure how useful it would be in my case; I'm using EXECUTE because I'm building a dynamic query based on user input, so the query plans would need to vary radically from run to run. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Which is faster SQL or PL/PGSQL
Joe Conway <[EMAIL PROTECTED]> writes: > Josh Berkus wrote: >> While we're on the topic, anyone know any good ways to speed up EXECUTE >> statements in PL/pgSQL functions? > Never tried it, but is it possible to use a prepared statement inside a > PL/pgSQL function? You could probably EXECUTE prepare and execute statements, but I don't see the point --- this would just be an extremely tedious way of duplicating plpgsql's normal query-plan-caching behavior. AFAICS, the whole point of EXECUTE in plpgsql is that it doesn't take any shortcuts, and so the answer to Josh's question can only be "don't use EXECUTE"... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Crosstab question
Adam Witney wrote:
The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.
Am I using this function correctly? What is supposed to happen with missing
values?
Yeah, that's a limitation of the version of crosstab distributed with
7.3. And (believe it or not) I've actually found cases where that
behavior is useful. There is another version of crosstab ("hashed"
crosstab) that treats missing values the way you expect. You can grab a
copy backported for 7.3 here:
http://www.joeconway.com/
You want the one that says: "contrib/tablefunc backported for PostgreSQL
7.3.x -- sync'd with CVS HEAD 02-Oct-2003"
HTH,
Joe
---(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] Which is faster SQL or PL/PGSQL
Tom, > AFAICS, the whole point of EXECUTE in plpgsql is that it doesn't take > any shortcuts, and so the answer to Josh's question can only be "don't > use EXECUTE"... Yeah, that's what I thought, I was just hoping for some low-hanging fruit. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] [postgres] PostgreSQL-DB auf Web-Schnittstelle bringen
On Mon, Oct 20, 2003 at 12:36:01PM +0200, Volker G?bbels wrote: > Und DB-Modelle sollte man eh besser grafisch entwickeln ... Richtig. Zettel und Stift sind genauso unersetzlich wie awk+sed. cu -- - Enrico Weigelt== metux IT services phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] converting an oracle procedure to postgres
I have read the docs on converting Oracle to Postgres already. I'm a little confused though. I have a procedure in Oracle that just does some calculations and then does an update based on the calculations. The procedure doesn't return anything. It seems like in Postgres that everything has to be a function and has to return something. The following procedure is almost converted to Postgres format but it is not quite correct yet: CREATE OR REPLACE PROCEDURE p_updateorders (decimal, date) AS ' DECLARE orderno ALIAS FOR $1; orderdate ALIAS FOR $2; --defining variables v_subtotal decimal; v_taxstatus varchar(1); v_shipping varchar(12); v_shippingrate decimal; V_shippingcharge decimal := 0; v_taxrate decimal := 0; v_taxamtdecimal; v_totalamt decimal; BEGIN --taking the subtotal by calcualting with right price and qty of products in an order SELECT SUM( product_price(orderdate,product_no, qty) * qty) INTO v_subtotal FROM orderline WHERE order_no = orderno GROUP BY order_no; --finding if tax applicable or not SELECT tax_status INTO v_taxstatus FROM orders WHERE order_no = orderno; --finding the shipping method SELECT shipping_method INTO v_shipping FROM orders WHERE order_no = orderno; --get the tax rate IF upper(v_taxstatus) = ''Y'' THEN SELECT tax_rate INTO v_taxrate FROM tax WHERE state = (SELECT state FROM customer WHERE customer_no = (SELECT distinct customer_no FROM orders WHERE order_no = orderno)); END IF; v_taxamt := v_taxrate * v_subtotal; --get shipping cost IF upper(v_shipping) = ''2DAY-AIR'' THEN v_shippingrate := .08; ELSIF upper(v_shipping) = ''1DAY-AIR'' THEN v_shippingrate := .1; ELSIF upper(v_shipping) = ''GROUND'' THEN v_shippingrate := .05; ELSE v_shippingrate := 0; END IF; v_shippingcharge := v_shippingrate * v_subtotal; --calculating the total amount v_totalamt := v_subtotal + v_taxamt + v_shippingcharge; --now update the ORDERS table with new values UPDATE orders SET subtotal = v_subtotal, tax_amt = v_taxamt, shipping_charge = v_shippingcharge, total_amt = v_totalamt WHERE order_no = orderno; END; ' LANGUAGE 'plpgsql'; I know I have to relpace the word PROCEDURE with FUNCTION but then it wants me to put RETURNS but I don't want to return anything. I was thinking that I could just have it return integer and then after the last update statement put return 0. I'd rather not have it return a junk value though. What should I do? Thanks, Clint _ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com ---(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] converting an oracle procedure to postgres
Clint, > v_taxstatus varchar(1); > v_shipping varchar(12); Drop the varchar limits, it's not supported inside PL/pgSQL. > I know I have to relpace the word PROCEDURE with FUNCTION but then it wants > me to put RETURNS but I don't want to return anything. I was > thinking that I could just have it return integer and then after the last > update statement put return 0. I'd rather not have it return a junk value > though. What should I do? I generally return integer or boolean for such functions. For example, you can have it return "TRUE" at the end, and then your client code can interpret any non-true result (Error message, null) as an error. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] converting an oracle procedure to postgres
"Clint Stotesbery" <[EMAIL PROTECTED]> writes: > I know I have to relpace the word PROCEDURE with FUNCTION but then it wants > me to put RETURNS but I don't want to return anything. You can say RETURNS VOID in recent releases. This is a bit of a hack but it expresses your intent ... you still have to use SELECT to invoke the function though. regression=# create or replace function fooey () returns void as ' regression'# begin regression'# raise notice ''fooey''; regression'# return; regression'# end' language plpgsql; CREATE FUNCTION regression=# select fooey(); NOTICE: fooey fooey --- (1 row) regression=# regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] [postgres] Deutsche PostgreSQL-Mailingliste unter postgresql.org
Hallo Allerseits, ich habe mit Marc Fournier vereinbart, dass wir eine deutsche PostgreSQL-Mailingliste unter postgresql.org anlegen können. Ich denke, das würde der Einheitlichkeit des Auftretens entgegen kommen, zumal es auch schon eine französische und eine türkische gibt. Was haltet ihr also davon, die ganze Operation dorthin zu verlegen? -- Peter Eisentraut [EMAIL PROTECTED] Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [postgres] Deutsche PostgreSQL-Mailingliste unter
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - -- Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Was haltet ihr also davon, die ganze Operation dorthin zu verlegen? ja, da bin ich eindeutig dafür. Dies dürfte auch den positiven Nebeneffekt haben, dass hier etwas mehr los ist und interessierte deutschsprachige Postgres-Nutzer eher auf die Liste aufmerksam machen -- wenn sie denn auf der Website erwähnt wird (wovon ich mal ausgehe) ;-) Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ ** ** ODEM.org-Tour: http://tour.odem.org/ ** -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/lCIiOndlH63J86wRAhNWAJ9EqjzTDFP+mq581g2kRcvKtqf8FwCgzyxJ VNk1LELn+exE/cJzuCH96QA= =FRxW -END PGP SIGNATURE- Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [postgres] Deutsche PostgreSQL-Mailingliste unter postgresql.org
On Mon, Oct 20, 2003 at 07:53:42PM +0200, Peter Eisentraut wrote: > Was haltet ihr also davon, die ganze Operation dorthin zu verlegen? Halte ich für sinnvoll. cu -- - Enrico Weigelt== metux IT services phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] assorted Postgres SQL/ORDBMS questions
1. You can raise exceptions but you can't catch exceptions in pgsql right? 2. Does Postgres support ORDBMS operations? Specifically I am wondering about the ability to define your own objects and create functions/procedures for the objects (e.g. object.method()). In Oracle I would use CREATE TYPE and CREATE TYPE BODY. Postgres' create type seems quite different than Oracle's version and they don't seem equivalent to each other. The Postgres version seems like it is for creating your own datatypes but not your own objets. I couldn't find any docs on this except on the SQL commands page. 3. Does it support nested tables? Again I couldn't find any info in the docs for this. 4. Can dates only be storied in -MM-DD format? I've looked over the documentation at http://www.postgresql.org/docs/7.3/static/functions-formatting.html and it seems that doing to_date(t_date,''DD-MON-'') should return 20-OCT-2003 but it returns 2003-10-20 no matter what I do. An example: CREATE OR REPLACE FUNCTION datetest() RETURNS date AS ' DECLARE t_date varchar; v_date date; BEGIN t_date := to_char(now(),''DD-MON-''); v_date := to_date(t_date,''DD-MON-''); RETURN v_date; END; ' LANGUAGE 'plpgsql'; SELECT datetest(); this returns: datetest -- 2003-10-20 I wanted it to return 20-OCT-2003 and the documentation suggests that I should be able to do that yet it doesn't actually do it. Now slightly different: CREATE OR REPLACE FUNCTION datetest() RETURNS varchar AS ' DECLARE t_date varchar; v_date date; BEGIN t_date := to_char(now(),''DD-MON-''); v_date := to_date(t_date,''DD-MON-''); RETURN t_date; END; ' LANGUAGE 'plpgsql'; SELECT datetest(); This returns: -- datetest 20-OCT-2003 This works fine but it is a varchar. I really want it to be stored like that but in a date type instead. Thanks for the answers! -Clint _ Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa ---(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] [postgres] Deutsche PostgreSQL-Mailingliste unter postgresql.org
Peter Eisentraut wrote: >Hallo Allerseits, > >ich habe mit Marc Fournier vereinbart, dass wir eine deutsche >PostgreSQL-Mailingliste unter postgresql.org anlegen können. Ich denke, >das würde der Einheitlichkeit des Auftretens entgegen kommen, zumal es >auch schon eine französische und eine türkische gibt. > >Was haltet ihr also davon, die ganze Operation dorthin zu verlegen? > > > ja das würde sehr viel sinn machen und die Liste würde bekannter werden Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] assorted Postgres SQL/ORDBMS questions
Clint, > 1. You can raise exceptions but you can't catch exceptions in pgsql right? right. We'd like to do exception-trapping, but nobody has offered to program it. > 2. Does Postgres support ORDBMS operations? > Specifically I am wondering about the ability to define your own objects > and create functions/procedures for the objects (e.g. object.method()). In > Oracle I would use CREATE TYPE and CREATE TYPE BODY. Postgres' create type > seems quite different than Oracle's version and they don't seem equivalent > to each other. The Postgres version seems like it is for creating your own > datatypes but not your own objets. I couldn't find any docs on this except > on the SQL commands page. Not with that syntax, no. Our ORDBMS functionality is more aimed at creating your own datatypes, domains, operators, aggregates, and similar. > 3. Does it support nested tables? > Again I couldn't find any info in the docs for this. No. Nor will it if I have any clout on the Hackers list; Nested tables are a Bad Idea and they Violate The Relational Standard. Arrays are as far as I am willing to go, and only in special cases. > 4. Can dates only be storied in -MM-DD format? Dates are stored in an internal format in order to ensure compliance with the SQL date standard. The DATE type is stored as an integer; the TIMESTAMP is (I believe) binary. Depending on your locale, the default *representation* of dates may be -mm-dd, or something else. > I've looked over the documentation at > http://www.postgresql.org/docs/7.3/static/functions-formatting.html and it > seems that doing to_date(t_date,''DD-MON-'') should return 20-OCT-2003 > but it returns 2003-10-20 no matter what I do. You want to re-format the date; see the docs on to_char(). > This works fine but it is a varchar. I really want it to be stored like > that but in a date type instead. No, you don't need it to be stored that way. If you want dates to display a particular way, use to_char() when you query them. Dates are stored as dates, not as strings. BTW, MS SQL Server's implementation of DATETIME sucks rocks and violates the SQL standard besides. So don't go comparing them on me. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] assorted Postgres SQL/ORDBMS questions
Josh Berkus <[EMAIL PROTECTED]> writes: >> 4. Can dates only be storied in -MM-DD format? > Dates are stored in an internal format in order to ensure compliance with the > SQL date standard. The DATE type is stored as an integer; the TIMESTAMP is > (I believe) binary. Just to clarify: dates are stored as an integer number of days before or after some "day zero" (which is probably 1/1/1970 or 1/1/2000, but I forget at the moment). Timestamps are stored as a possibly fractional number of seconds before or after the timestamp origin, which I do recall is midnight 1/1/2000. These representations are compact to store and are eminently suitable for datetime arithmetic. They have nothing whatever to do with the input or output string representation; there is a ton of code in there to get from the one to the other. > Depending on your locale, the default *representation* > of dates may be -mm-dd, or something else. See the DATESTYLE parameter setting for some discussion of your options here. Also, to_date, to_timestamp, and to_char are available for special-purpose format conversions when no existing datestyle makes you happy. I quite concur with Josh that there is no percentage in storing dates or times as strings. Use the provided datatypes --- there's a huge amount of useful infrastructure in there. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
