Re: [SQL] No Documentation for to_char(INTERVAL, mask)
On Tue, Mar 06, 2001 at 09:22:25AM -0800, Josh Berkus wrote: > Karel, > > > The 'interval' version of to_char() isn't implemented -- may be in 7.2 > > (it's high in my TODO list:-) > > Grazie. (One of the things I love about PostgreSQL is being able to > get definitive answers on functionality -- try asking Microsoft an "is > this implemented?" question!) :-) > Given the lack of to_char(interval), I'd like to write a PLPGSQL > function to fill the gap in the meantime. If you can answer a few > questions about how interval values work, it would be immensely helpful: > > 1. Hours, minutes, and seconds are displayed as "00:00:00". Days are > displayed as "0 00:00:00". How are weeks, months, and years displayed? > > 2. If months have their own placeholder in the Interval data type, how > many days make up a month? Is it a fixed value, or does it depend on > the calendar? A displayed format is external string alternate of a internal number based form. A interval/timestamp string that you use in SQL is parsed to 'tm' struct (see man ctime) where has each item like hours, minutes own field. For some date/time operation is used Julian date (..etc) -- internaly PG not works with strings for date/time. I mean is too much difficult write a 'interval' to_char() version in some procedural language without access to real (internal) form of 'interval'. Big date/time guru is Thomas (CC:), comments? Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems with RULE
From: "Jens Hartwig" <[EMAIL PROTECTED]> > Hello Richard, > > this was a very precise analysis - thanks for the effort you made! Precisely wrong in this case. My mistakes have some of the finest tolerances in the world. 8-) > Nevertheless the Tom's explanation of the behaviour in case of views was > sufficient for me. But still I don't understand the behaviour in my case ... Yep - thanks Tom (another item for my notebook). I _think_ I understand Tom's explanation of your case - does this make sense? You have CREATE RULE r1 ON t_xyz WHERE old.deleted=false ... So - if you issue "DELETE FROM t_xyz" you'd want two things to happen: 1. where deleted is false set it to true 2. where deleted was true delete the record So - PG rewrites the query into two parts: DELETE FROM t_xyz WHERE old.deleted=false DELETE FROM t_xyz WHERE NOT(old.deleted=false) Unfortunately, the changes from the first part are visible to the second part so you end up marking everything for deletion then deleting it. Of course in your case you were selecting id=1 so it wasn't so obvious. I think that's what's happening here. Unfortunately, setting DEBUG_PRINT_xxx doesn't seem to show any detail, do I can't show a trace. Of course, with a trigger you can have an IF..THEN..ELSE to make sure you control the order of execution. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Quick question MySQL --> PgSQL
When I did the same task - I've writting a script on the perl, and if you have stable databese structure, I think, it's a best way - write a script which will run by cron /on-demand. Because, I don't remember exactly why (date format is different or something else) but, I couldn't move data from MySQl to text-file and from text-file to PostgreSQL directly, without transformation. Mar 6, 09:26 -0800, Josh Berkus has written: > Folks, > > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? > > Sorry to bother everyone with something that isn't strictly a SQL > question, but I'm not sure where else to ask. > > -Josh Berkus > > P.S. If somebody wants consulting $$$ for the above, it may be > available. > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 565-7293 >for law firms, small businesses fax 621-2533 > and non-profit organizations. 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]) > > - Grigoriy G. Vovk ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] random
On Mon, Mar 05, 2001 at 02:12:15PM -0500, Jelle Ouwerkerk wrote:
> Hi
>
> What would be the best way to select a random row from a result set?
>
> Possibilities:
>
> 1) o get the total number of rows using count()
>o generate a random number between 1 and the total
>o select the n'th row using OFFSET
>
> 2) o get the total number of rows using count()
>o generate a random number between 1 and the total
>o fetch n times
Here's my approach. It allows to get random row from the table, not from result
set in general. Maybe you will be able to modify it to fit your needs.
The table, which we want to get random row from, is not shown here. Let's
assume that it has primary key on integer type column. In the table rnd we keep
references to this table in value column.
Other columns are needed by our selection mechanism. We may want 'values' to be
duplicated (different weights), so there is this 'id' column which uniquely
identifies rnd's row.
CREATE SEQUENCE rnd_seq;
CREATE TABLE rnd (
id INT4 NOT NULL DEFAULT NEXTVAL('rnd_seq'),
r DOUBLE PRECISION NOT NULL DEFAULT random(),
value INT4,
PRIMARY KEY (id)
);
/* My idea is to index this table with random values.
* Then it is sufficent to take first row in this order to get a random one
* and of course we have to modify its r (random) fields not to get it again,
* and again, ...
*
* I hope you understand my explanation in weak english */
CREATE INDEX rnd_r_idx ON rnd (r);
/* This function does it. SELECT it issues is very fast, 'cause it uses an
* index */
CREATE FUNCTION get_rnd() RETURNS INT4 AS '
DECLARE
rowid INT4;
val INT4;
BEGIN
SELECT id, value INTO rowid, val
FROM rnd
ORDER BY r
LIMIT 1;
IF NOT FOUND THEN RETURN NULL; END IF;
UPDATE rnd SET r = random() WHERE id = rowid;
RETURN val;
END;
' LANGUAGE 'plpgsql';
/* This function only fills the table with test data */
CREATE FUNCTION fill() RETURNS INT4 AS '
DECLARE
i INT4;
BEGIN
i := ;
WHILE i <> 0 LOOP
INSERT INTO rnd (value) VALUES (i);
i := i - 1;
END LOOP;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
VACUUM ANALYZE rnd;
What do you think? Is it a good idea?
greetings,
Tom
--
.signature: Too many levels of symbolic links
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] random
On Wed, Mar 07, 2001 at 12:50:36PM +0100, Tomek Zielonka wrote: > On Mon, Mar 05, 2001 at 02:12:15PM -0500, Jelle Ouwerkerk wrote: > > Hi > > > > What would be the best way to select a random row from a result set? > > > > Here's my approach. It allows to get random row from the table, not from result > set in general. Maybe you will be able to modify it to fit your needs. [cut] > What do you think? Is it a good idea? I tested it myself and unfortunately it doesn't work as should. I have to rethink it. greetings, Tom -- .signature: Too many levels of symbolic links ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] sql functions and triggers
Hello! It is possible to pass ie. insert fields and values to sql function in trigger? I want to define a trigger on insert (select delete etc.) and log some values in different table(s). -- "Code reviews are like sex, just anyone can do it, but skill and training can make you a lot better at it." - LJ Thomas ---(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] sql functions and triggers
On Wed, Mar 07, 2001 at 03:03:59PM +0100, Andrzej Roszkowski wrote: > Hello! > > It is possible to pass ie. insert fields and values to sql function in > trigger? I want to define a trigger on insert (select delete etc.) and log > some values in different table(s). Sure, see docs about SPI interface or you can try use RULEs for this. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
Thomas, Karel, > I agree with Karel's point that it may be a pain to use a procedural > language to manipulate a "stringy" interval value. If you use a C > function instead, you can get access to the internal manipulation > functions already present, as well as access to system functions to > manipulate a tm structure. Ah, but this leaves out two important considerations of my particular problem: 1. The interval I want to manipulate is limited to a relative handful of possibilities (it's a billing interval): 1 week, 2 weeks, 2x/Month, 1 month, 2 months, 3 months. 2. I don't do C. And I don't have the budget to hire somebody to di it in C. If this was a bigger budget project, I'd simply take Karel's notes and hire a programmer to create the to_char(Interval) function and thus contribute to PostgreSQL ... but this project is over budget and behind schedule already. I'll take a stab at in in PLPGSQL and post the results. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: AW: [SQL] Problems with RULE
"Jens Hartwig" <[EMAIL PROTECTED]> writes: > What would have happened, if I executed an unconditional DELETE? > => DELETE FROM t_xyz; > Which statement would have been generated by PostgreSQL in this case? Unfortunately, I didn't keep the prior discussion, so I don't remember exactly what the rule was. But the general idea for conditional rules is that we generate rule-action WHERE rule-action's-own-conditions AND rule-condition AND conditions-from-original-query (repeat for each action of each relevant rule) and then if we didn't find any relevant unconditional INSTEAD rules, we generate original-query-action WHERE conditions-from-original-query AND NOT (conditions-of-conditional-INSTEAD-rules) There's a more extensive discussion in the Programmer's Guide, http://www.postgresql.org/devel-corner/docs/postgres/rules.html regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: [DOCS] Extending PostgreSQL Using C
Hannu Krosing <[EMAIL PROTECTED]> writes: > Boulat Khakimov wrote: >> ERROR: Can't find function encrypt in file /[full path here]/encrypt.so > Can _postgres_ user read /[full path here]/encrypt.so ? Presumably so. If he were unable to load the .so file, he'd be getting a different error message. This message indicates that he got past the load step, but dl_sym is unable to resolve the symbol "encrypt". I asked about the symbol names shown by nm(1), but got no answer ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
AW: [SQL] Problems with RULE
> [...] > So - if you issue "DELETE FROM t_xyz" you'd want two things to happen: > > 1. where deleted is false set it to true > 2. where deleted was true delete the record > > So - PG rewrites the query into two parts: > > DELETE FROM t_xyz WHERE old.deleted=false > DELETE FROM t_xyz WHERE NOT(old.deleted=false) > [...] Oh ... that's it! I hit my head against the wall :-) Thank you very much! Best regards, Jens Hartwig - T-Systems Projektleiter debis Systemhaus GEI GmbH Hausanschrift: Eichhornstraße 3, 10785 Berlin Postanschrift: 10785 Berlin Telefon: (004930) 25 54-32 82 Telefax: (004930) 25 54-31 87 Mobiltelefon: (0170) 167 26 48 E-Mail: [EMAIL PROTECTED] Internet: http://www.t-systems.de ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] how to get info of function
Hi, What system table hold info of user defined function?? Acturally , I want to know how to retrive the defination of a function from its name. Thankes. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
> I'll take a stab at in in PLPGSQL and post the results. OK. date_part() is your friend ;) - Thomas ---(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] Newbie: execute function error!
I created a sample function:
CREATE FUNCTION concat_text (text, text) RETURNS text AS '
BEGIN
RETURN $1 || $2;
END; '
LANGUAGE 'plpgsql';
The creation was fine. But when I execute the function with : select
concat('a', 'b');
I get the error:
NOTICE: plpgsql: ERROR during compile of concat_text near line 1
"RROR: parse error at or near "
What did I do wrong?
Thanks!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] work on rows
Hello, I have a problem with a sql query.I have two tables : the first contains categories and the second subcategories. What kind of select may I use to get something like Yahoo! categories ( each "main" category gets *at most* three sub-categories.. Thanks Regards, Rachel
Re: [SQL] Permissons on database
> "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes:
bk> How do I grant permissions on everything in the selected
bk> databes?
bk> GRANT doesnt take as on object database name nor does it
bk> accept wild chars
Attached is some Perl code I wrote long ago to do this. This
particular code was done for Keystone, a problem tracking database and
it would do a "GRANT ALL". Modify it as needed. Last I checked it
worked with both PostgreSQL 6.5.x and 7.0.x
#! /usr/bin/perl -w
# I'm also appending a Perl script to grant public access to all
# keystone tables. It uses the Pg module for PostgreSQL, so you will
# need to add that first. However, I find it a bit less tedious than
# granting access by hand
# Roland B. Roberts, PhD Custom Software Solutions
# [EMAIL PROTECTED] 101 West 15th St #4NN
# [EMAIL PROTECTED] New York, NY 10011
use Pg;
if (defined $ARGV[0]) {
$dbname = $ARGV[0];
} else {
$dbname = "keystone";
}
print "connecting to $dbname\n";
$dbh = Pg::connectdb("dbname=$dbname $ARGV[1]");
die "Pg::connectdb failed, $dbh->errorMessage"
unless ($dbh->status == PGRES_CONNECTION_OK);
$c{relname} = $dbh->exec ("select relname from pg_class where relname !~ '^pg_' and
relkind != 'i'");
die "Pg::exec, $dbh->errorMessage"
unless ($c{relname}->resultStatus == PGRES_TUPLES_OK);
for ($i = 0; $i < $c{relname}->ntuples; $i++) {
$relname = $c{relname}->getvalue($i,0);
print "grant all on $relname to public\n";
$c{grant} = $dbh->exec ("grant all on $relname to public");
die "Pg::exec, ".$dbh->errorMessage
unless ($c{grant}->resultStatus == PGRES_COMMAND_OK);
}
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED] Forest Hills, NY 11375
---(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] work on rows
Author: Rachel Coin <[EMAIL PROTECTED]> I have a problem with a sql query.I have two tables : the first contains categories and the second subcategories. What kind of select may I use to get something like Yahoo! categories ( each "main" category gets *at most* three sub-categories.. Do you mean something like the following? (PS - please don't post HTML to mailing lists) richardh=> select * from cats; c --- A B (2 rows) richardh=> select * from subcats; c | s ---+ A | a1 A | a2 A | a3 A | a4 (4 rows) richardh=> select cats.c,subcats.s from cats,subcats where cats.c=subcats.c; c | s ---+ A | a1 A | a2 A | a3 A | a4 (4 rows) richardh=> select cats.c,subcats.s from cats,subcats where cats.c=subcats.c and subcats.s in (select s from subcats where subcats.c=cats.c limit 2); c | s ---+ A | a1 A | a2 (2rows) - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Newbie: execute function error!
On 3/6/01, 5:55:05 AM, John GM <[EMAIL PROTECTED]> wrote regarding [SQL] Newbie:
execute function error!:
> I created a sample function:
> CREATE FUNCTION concat_text (text, text) RETURNS text AS '
> BEGIN
> RETURN $1 || $2;
> END; '
> LANGUAGE 'plpgsql';
> The creation was fine. But when I execute the function with : select
> concat('a', 'b');
> I get the error:
> NOTICE: plpgsql: ERROR during compile of concat_text near line 1
> "RROR: parse error at or near "
> What did I do wrong?
Nothing wrong with your function, but the error seems to be missing an E
at the front (unless it's a typo) - you didn't cut and paste the
definition from a Windows machine to *nix did you - leaves CR characters
lying around.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] quotes in pl/pgsql
Hi all, I just want to know how to put quotes around a string. Is there a function to do so? If not how can I escape a single quote. Thank you in advance. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] A query that doesn't work on 7.1
Here's a query that doesn't work on 7.1. Is this a bug or am I doing
something wrong?
The last two selects yield:
ERROR: Sub-SELECT uses un-GROUPed attribute m1.ropnum from outer
query
Basically, everything works until I apply the avg() function and try
to aggregate the results.
drop table mtr;
create table mtr (
ttype varchar(2),
--record type
ropnum int4,
--order number
minum int4,
--item number
pnum
varchar(18),
tdate date,
primary key (ttype,ropnum,minum)
);
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('po',1000,1,2000,'2001-Jan-30');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1000,1,2001,'2001-Jan-10');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1000,2,2002,'2001-Jan-12');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1000,3,2003,'2001-Jan-14');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('po',1001,1,2000,'2001-Feb-28');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1001,1,2011,'2001-Feb-01');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1001,2,2012,'2001-Feb-02');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values ('wm',1001,3,2013,'2001-Feb-03');
--The finish date is represented by the tdate of a po type record
--The start date is found by the earliest of the wm type records
with the same ropnum,minum fields
--This lists the start and finish dates
select
(select min(tdate) from mtr where ttype = 'wm'
and ropnum = m1.ropnum) as start,
m1.tdate as finish
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
;
--Now I try to find the average number of days between start and
finish for the part
select
avg(date_part('day',(start::datetime - finish::datetime)::timespan))
from
(select
(select min(tdate) from
mtr where ttype = 'wm' and ropnum = m1.ropnum) as start,
m1.tdate::datetime as
finish
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
) as dates
;
--Here I try a different method
-- select
select
avg(date_part('day',((select min(tdate) from
mtr where ttype = 'wm' and ropnum = m1.ropnum)::datetime - m1.tdate::datetime)::timespan))
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
;
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] quotes in pl/pgsql
Najm Hashmi wrote: > > Hi all, I just want to know how to put quotes around a string. Is there a > function to do so? > If not how can I escape a single quote. > Thank you in advance. > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] single qutoe escaping : 'here''s johnny' ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] A query that doesn't work on 7.1
Kyle <[EMAIL PROTECTED]> writes: > Here's a query that doesn't work on 7.1. Is this a bug or am I doing > something wrong? Hmm, I think you have found some subtle bugs in aggregate processing; offhand it seems like both these queries should be legal. I'll see what I can do about it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] quotes in pl/pgsql
Hi Najm, Is this what you mean? CREATE FUNCTION foobar(int4) returns int4 as 'DECLARE textbuf varchar(120); BEGIN textbuf := ''Example Text ''; insert into sometable (something) values (textbuf); RETURN 0; END;' LANGUAGE 'plpgsql'; Najm Hashmi wrote: > > Hi all, I just want to know how to put quotes around a string. Is there a > function to do so? > If not how can I escape a single quote. > Thank you in advance. > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] quotes in pl/pgsql
On Thu, Mar 08, 2001 at 05:54:38PM -0500, Najm Hashmi wrote: > Hi all, I just want to know how to put quotes around a string. Is there a > function to do so? > If not how can I escape a single quote. Others have answered how to quote a single string. I wrote an entire section on quotes on the PL/SQL-to-PL/pgSQL-HOWTO. It's called "Quote me on that" and mentions several cases on quote usage in PL/pgSQL and what to do about it. http://www.brasileiro.net/roberto/howto -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer DOS = Damned Old Software ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] A query that doesn't work on 7.1
>> Here's a query that doesn't work on 7.1. Is this a bug or am I doing
>> something wrong?
> Hmm, I think you have found some subtle bugs in aggregate processing;
> offhand it seems like both these queries should be legal. I'll see what
> I can do about it.
Not so subtle after all :-(. Curious that no one noticed this before.
Here's the patch if you need it right away...
regards, tom lane
*** src/backend/optimizer/util/clauses.c.orig Mon Feb 12 20:26:43 2001
--- src/backend/optimizer/util/clauses.cWed Mar 7 20:49:01 2001
***
*** 540,545
--- 540,553
Query
*context)
{
if (node == NULL)
+ return false;
+
+ /*
+* If we find an aggregate function, do not recurse into its
+* arguments. Subplans invoked within aggregate calls are allowed
+* to receive ungrouped variables.
+*/
+ if (IsA(node, Aggref))
return false;
/*
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] how to reload a function
I have 2 plpgsql defined functions, say: create function A() returns ... as' . 'language 'plpgsql'; create function B() returns ... as ' declare begin select A(..) into myvar from end; 'language 'plpgsql'; If I modify function A (drop && re_create), then I have to re_create function B though no change to function B. Is there any way (sql stmt) let me re_load function B's defination without using drop and create?? Thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] postgresql beta-4,5 BUG
DELPHI can't use TABLE component ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Buglet?
chris=# select *, array_element_count(pupils_on_leave) as
number_awol_pupils from absence ;
date | lesson_number | pupils_on_leave | pupils_awol
| number_awol_pupils
+---+-+---+
2001-03-08 00:00:00+13 |25 | | {4,29,3} |
2001-03-08 00:00:00+13 |26 | | {17,27,28,14} |
2001-03-08 00:00:00+13 |27 | | {5,24,13} |
2001-03-08 00:00:00+13 |12 | {12,34} | {23}
| 2
2001-03-07 00:00:00+13 |21 | | {4,5,28,15} |
(5 rows)
As expected.
chris=# update absence set pupils_on_leave[1]=9 where lesson_number=21;
UPDATE 1
Now I tell pg to insert a value in the array, & he says he's done it.
chris=# select *, array_element_count(pupils_on_leave) as
number_awol_pupils from absence ;
date | lesson_number | pupils_on_leave | pupils_awol
| number_awol_pupils
+---+-+---+
2001-03-08 00:00:00+13 |25 | | {4,29,3} |
2001-03-08 00:00:00+13 |26 | | {17,27,28,14} |
2001-03-08 00:00:00+13 |27 | | {5,24,13} |
2001-03-08 00:00:00+13 |12 | {12,34} | {23}
| 2
2001-03-07 00:00:00+13 |21 | | {4,5,28,15}
|
(5 rows)
But he has told me a fat fib.
chris=# \d absence
Table "absence"
Attribute| Type | Modifier
-+--+--
date| timestamp with time zone |
lesson_number | integer |
pupils_on_leave | integer[]|
pupils_awol | integer[]|
It that a bug?
It would be very nice for me if that worked.
( you have to put '{}' in the array field before usisng the field[n]=x
notation. )
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
---(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] No Documentation for to_char(INTERVAL, mask)
> > Given the lack of to_char(interval), I'd like to write a PLPGSQL > > function to fill the gap in the meantime... > I mean is too much difficult write a 'interval' to_char() version in > some procedural language without access to real (internal) form of > 'interval'. I agree with Karel's point that it may be a pain to use a procedural language to manipulate a "stringy" interval value. If you use a C function instead, you can get access to the internal manipulation functions already present, as well as access to system functions to manipulate a tm structure. A combination of contrib/ and src/backend/utils/adt/ information could give you a start on the C implementation (and that is rather easily moved into the backend later). I haven't tried the PL/PGSQL approach however. If you decide to proceed on that, let us know how it goes! - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
