Re: [SQL] Calling Functions in RULEs
Hi, I had no try wheter this solves your problem, but have you conciddered using an array or a self-written fcn which dicards your results i.E. SELECT my_discard_but_last( q1(), q2(), q3()); or SELECT ARRAY[ q1(), q2(), q3() ]; or SELECT ROW(q1(), q2(), q3()) as my_tripel_type; Have you considdered CASTs? These are just ideas |-Original Message- |From: Jan B. [mailto:[EMAIL PROTECTED] |Sent: Sonntag, 22. Mai 2005 11:24 |To: pgsql-sql@postgresql.org |Subject: [SQL] Calling Functions in RULEs | | |Hello, | |I would like to call multiple PL/pgSQL procedures (i.e. functions |returning void) from INSERT, UPDATE and DELETE RULEs. | |Using "SELECT some_procedure();" is a possible way to do that, but it |causes a dummy result table to be delivered to the application |invoking |the INSERT/UPDATE/DELETE. When using multiple SELECTs to call multiple |functions inside the RULE, multiple result tables will be delivered to |the application, which is very unhandy and causes trouble especially |when using asynchronous command processing. Though "psql" does |only show |the last result table, all dummy result tables have to be fetched by |PGgetResult(), when using asynchronous mode with libpq. | |Is there any tidy way to call a void-function inside a RULE without |creating a result table that is passed to the application? |Using a command like "UPDATE dummy_table SET dummy=1 WHERE |some_procedure() NOTNULL;" would work, but that would be very dirty. | |Does a command like "PERFORM" (from PG/pgSQL) exist in SQL too, which |discards the (void) result of the function call? | | |Thanks for helping | |Jan Behrens ||| | |---(end of |broadcast)--- |TIP 2: you can get off all lists at once with the unregister command |(send "unregister YourEmailAddressHere" to |[EMAIL PROTECTED]) | ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Need clarification
how can retrieve rows where the datedifference is more than 30... assume there exists a field named 'datacreated' which is of type 'date'. I need to compare it with the current date and need to extract the matched rows thanks in advance. Palanivel Important Email Information :- The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. If you are not the intended addressee please contact the sender and dispose of this e-mail immediately. ---(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] Need clarification
O [EMAIL PROTECTED] έγραψε στις May 23, 2005 : > > > > > how can retrieve rows where the datedifference is more than 30... more than 30 in what units? years,days,milliseconds?? > assume there exists a field named 'datacreated' which is of type 'date'. I > need to compare it with the current date and need to extract the matched > rows > select * from footable where datacreated = now()::date; > thanks in advance. > > Palanivel > > Important Email Information :- The information in this email is > confidential and may be legally privileged. It is intended solely for > the addressee. Access to this email by anyone else is unauthorized. If > you are not the intended recipient, any disclosure, copying, distribution or > any action taken or omitted to be taken in reliance on it, is prohibited > and may be unlawful. If you are not the intended addressee please contact > the sender and dispose of this e-mail immediately. > > > ---(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 > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Need clarification
Your're looking for the interval data type. timestamp - timestamp or date - date can be compared with interval CURRENT_TIMESTAMP - '4d'::interval => today - 4 days |-Original Message- |From: [EMAIL PROTECTED] |[mailto:[EMAIL PROTECTED] |Sent: Montag, 23. Mai 2005 11:52 |To: pgsql-sql@postgresql.org |Subject: [SQL] Need clarification | | | | | | |how can retrieve rows where the datedifference is more than 30... |assume there exists a field named 'datacreated' which is of |type 'date'. I |need to compare it with the current date and need to extract |the matched |rows | |thanks in advance. | |Palanivel | |Important Email Information :- The information in this email is |confidential and may be legally privileged. It is |intended solely for |the addressee. Access to this email by anyone else is |unauthorized. If |you are not the intended recipient, any disclosure, copying, |distribution or |any action taken or omitted to be taken in reliance on it, is |prohibited |and may be unlawful. If you are not the intended addressee |please contact |the sender and dispose of this e-mail immediately. | | |---(end of |broadcast)--- |TIP 3: if posting/reading through Usenet, please send an appropriate | subscribe-nomail command to [EMAIL PROTECTED] so that your | message can get through to the mailing list cleanly | ---(end of broadcast)--- TIP 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] need date clarification
how can i extract the system date in postgress. Thanks & Regards Palanivel P.K Important Email Information :- The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. If you are not the intended addressee please contact the sender and dispose of this e-mail immediately. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [despammed] [SQL] need date clarification
am 23.05.2005, um 16:26:23 +0530 mailte [EMAIL PROTECTED] folgendes: > > > > > > how can i extract the system date in postgress. versand=# select now(); now --- 2005-05-23 13:26:13.353826+02 (1 Zeile) versand=# select CURRENT_DATE; date 2005-05-23 (1 Zeile) Regards, 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Need clarification
Sorry, I didn't get ur point...please, tell me exact query to extract the rows from a table A which contains a date field named DTE whose difference is 30 days as compared to the current date... Thanks & Regards Palanivel P.K KÖPFERL Robert <[EMAIL PROTECTED]To: "'[EMAIL PROTECTED]'" norys.at> <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org Sent by: cc: [EMAIL PROTECTED]Subject: Re: [SQL] Need clarification tgresql.org 05/23/2005 04:34 PM Your're looking for the interval data type. timestamp - timestamp or date - date can be compared with interval CURRENT_TIMESTAMP - '4d'::interval => today - 4 days |-Original Message- |From: [EMAIL PROTECTED] |[mailto:[EMAIL PROTECTED] |Sent: Montag, 23. Mai 2005 11:52 |To: pgsql-sql@postgresql.org |Subject: [SQL] Need clarification | | | | | | |how can retrieve rows where the datedifference is more than 30... |assume there exists a field named 'datacreated' which is of |type 'date'. I |need to compare it with the current date and need to extract |the matched |rows | |thanks in advance. | |Palanivel | |Important Email Information :- The information in this email is |confidential and may be legally privileged. It is |intended solely for |the addressee. Access to this email by anyone else is |unauthorized. If |you are not the intended recipient, any disclosure, copying, |distribution or |any action taken or omitted to be taken in reliance on it, is |prohibited |and may be unlawful. If you are not the intended addressee |please contact |the sender and dispose of this e-mail immediately. | | |---(end of |broadcast)--- |TIP 3: if posting/reading through Usenet, please send an appropriate | subscribe-nomail command to [EMAIL PROTECTED] so that your | message can get through to the mailing list cleanly | ---(end of broadcast)--- TIP 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 Important Email Information :- The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. If you are not the intended addressee please contact the sender and dispose of this e-mail immediately. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [despammed] Re: [SQL] Need clarification
am 23.05.2005, um 16:31:55 +0530 mailte [EMAIL PROTECTED] folgendes:
>
>
>
>
> Sorry, I didn't get ur point...please, tell me exact query to extract the
> rows from a table A which contains a date field named DTE whose difference
> is 30 days as compared to the current date...
test=# create table datediff (start date);
CREATE TABLE
test=# insert into datediff values ('1966/08/30');
INSERT 153382679 1
test=# insert into datediff values ('2005/05/23');
INSERT 153382680 1
test=# select * from datediff ;
start
1966-08-30
2005-05-23
(2 Zeilen)
test=# select * from datediff where CURRENT_DATE - start > 10;
start
1966-08-30
(1 Zeile)
test=# select * from datediff where CURRENT_DATE - start < 10;
start
2005-05-23
(1 Zeile)
Btw.: please no TOFU, read http://en.wikipedia.org/wiki/TOFU
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Hi,
why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?
Markus
--
Markus Bertheau <[EMAIL PROTECTED]>
signature.asc
Description: This is a digitally signed message part
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Markus Bertheau wrote:
Hi,
why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?
Markus
Perhaps Arrays always have to contain at least one element? (I don't
know for sure.)
SELECT array[];
ERROR: syntax error at or near "]" at character 14
LINE 1: SELECT array[];
SELECT array[1];
array
---
{1}
(1 row)
---(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] ARRAY() returning NULL instead of ARRAY[] resp. {}
Dnia 23-05-2005, pon o godzinie 18:54 +, Jan B. napisał(a):
> Perhaps Arrays always have to contain at least one element? (I don't
> know for sure.)
They can:
template1=# select '{}'::TEXT[];
text
--
{}
(1 запись)
I don't know, why the ARRAY[] syntax doesn't work for empty arrays.
Markus
--
Markus Bertheau <[EMAIL PROTECTED]>
signature.asc
Description: This is a digitally signed message part
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Markus Bertheau wrote:
why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?
Why would you expect an empty array instead of a NULL? NULL is what
you'd get for other data types -- for example:
regression=# SELECT (SELECT 1 WHERE FALSE) IS NULL;
?column?
--
t
(1 row)
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Joe Conway <[EMAIL PROTECTED]> writes:
> Markus Bertheau wrote:
>> why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
>> ARRAY[] resp. '{}'?
> Why would you expect an empty array instead of a NULL?
I think he's got a good point, actually. We document the ARRAY-with-
parens-around-a-SELECT syntax as
The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output column.
To me, that implies that a subquery result of no rows generates a
one-dimensional array of no elements, not a null array.
This is not the same as
SELECT ARRAY[(SELECT 1 WHERE FALSE)];
We define a scalar subquery that returns no rows as returning null, so
this is equivalent to
SELECT ARRAY[NULL];
which *ought* to yield an array containing a single NULL element,
but since we can't yet handle arrays containing nulls we punt and
return a null array value. That's wrong too ... but it's a different
issue. The point Markus is complaining about seems like it should
be easily fixable.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Help: Function for splitting VARCHAR column and migrating its data to 2 new tables
I am restructuring my DB schema and need help migrating data from 1 column of an existing table to two new tables. I have some Java code that can do this for me, but it's very slow, and I am now hoping I can migrate this data with some clever SQL instead. Here are my 3 tables: user_data (existing, old table) - id (PK), user_id (FK) keywords VARCHAR(256) -- this contains comma separated keywords -- e.g. "new york,san francisco, dallas, food" -- also "keywords without strings are really just 1 keyword" add_date TIMESTAMP So now I'm trying to migrate this "keywords" VARCHAR column to a more normalized schema: user_data_keyword (new lookup table to populate) - id (PK) -- I may change PK to PK(user_data_id, keyword_id) user_data_id (FK) keyword_id (FK) keyword (new table to populate) --- id (PK) name VARCHAR(64) NOT NULL UNIQUE add_date TIMEZONE I just found http://www.postgresql.org/docs/current/static/functions-string.html , but if anyone could lend me a hand by getting me started with writing a function for this, I'd really appreciate it. Thanks, Otis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Dnia 24-05-2005, wto o godzinie 00:06 -0400, Tom Lane napisał(a):
> Joe Conway <[EMAIL PROTECTED]> writes:
> > Markus Bertheau wrote:
> >> why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
> >> ARRAY[] resp. '{}'?
>
> > Why would you expect an empty array instead of a NULL?
>
> I think he's got a good point, actually. We document the ARRAY-with-
> parens-around-a-SELECT syntax as
>
> The resulting one-dimensional array will have an element for
> each row in the subquery result, with an element type matching
> that of the subquery's output column.
>
> To me, that implies that a subquery result of no rows generates a
> one-dimensional array of no elements, not a null array.
>
> The point Markus is complaining about seems like it should
> be easily fixable.
Great :) Is this a TODO?
--
Markus Bertheau <[EMAIL PROTECTED]>
signature.asc
Description: This is a digitally signed message part
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
O Joe Conway έγραψε στις May 23, 2005 :
> Markus Bertheau wrote:
> > why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
> > ARRAY[] resp. '{}'?
> >
>
> Why would you expect an empty array instead of a NULL? NULL is what
> you'd get for other data types -- for example:
One could ask in the same fashion why someone would want a table
if this table contains no rows.
A null value may mean "dont know",
wheras a '{}' (empty) value may mean "empty set".
For instance lets consider the case where an array holds
the factors of a polynomial formula.
An null value might mean that the person defining
the formulas haven't been bothered with this one yet.
An empty value might mean that the person indicates
that has worked on this particular one, but he/she has no data yet.
Ok extreme cases, but to me there is a clean distinction
between a null array and an empty array.
Also what is definately needed is arrays that may contain
null values.
>
> regression=# SELECT (SELECT 1 WHERE FALSE) IS NULL;
> ?column?
> --
> t
> (1 row)
>
> Joe
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
--
-Achilleus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
