Re: [SQL] Stuffing six separate columns into a single array?
On Mon, Oct 04, 2004 at 10:56:20PM -0500, C. Bensend wrote: > >I have a table with the following columns: > > dns1_ptr | inet | default '0.0.0.0'::inet > dns2_ptr | inet | default '0.0.0.0'::inet > dns3_ptr | inet | default '0.0.0.0'::inet > dns4_ptr | inet | default '0.0.0.0'::inet > dns5_ptr | inet | default '0.0.0.0'::inet > dns6_ptr | inet | default '0.0.0.0'::inet > >(yes, I know, I didn't know any better) > >It is being replaced by: > > dns_ptr| inet[] | default ...etc > >(hopefully this is more intelligent) How does dns_ptr relate to other data? Depending on what you're doing, other ways of organizing your tables might also make sense. Here's an example: CREATE TABLE hosts ( idSERIAL PRIMARY KEY, hostname VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE dns_servers ( id SERIAL PRIMARY KEY, ipaddr INET NOT NULL UNIQUE ); CREATE TABLE host_dns ( hostid INTEGER REFERENCES hosts, dnsid INTEGER REFERENCES dns_servers, UNIQUE(hostid, dnsid) ); >Now, as I migrate the data from the old table to the new, is there > any way to just do the typical 'INSERT INTO blah SELECT a,b,c FROM blah2' > type of thing? ie, > > INSERT INTO new_table ( dns_ptr ) SELECT dns1_ptr, dns2_ptr .. FROM >old_table; If none of the dnsX_ptr values can be NULL, then try this: INSERT INTO new_table (dns_ptr) SELECT ARRAY[dns1_ptr, dns2_ptr, dns3_ptr, dns4_ptr, dns5_ptr, dns6_ptr] FROM old_table; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Howto turn an integer into an interval?
Hi List, I got a table named foobar with two fields last_sms is a 'timestamp without timezone' resend_interval is a 'integer' I choose 'integer' for resend_interval rather than a 'interval' because the frontend can easier handle the number of seconds. But now I'm stuck with the query to get all rows that we're older than 'resend_interval' in seconds. My first try was: SELECT * FROM sms_groups WHERE (current_timestamp - last_sms) > '3600 seconds'::interval This is working great and returns all rows that are older than a hour. But how I do this with the colum 'resend_interval' in the query? SELECT * FROM sms_groups WHERE (current_timestamp - last_sms) > resend_interval::interval gives me only a "cannot cast type integer to interval". So how can I turn an integer to an interval? Or is there an other solution? Suggestions? P.S: I'm using psql '7.4.5' of gentoo. -- So long... Fuzz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Howto turn an integer into an interval?
O Erik Wasser έγραψε στις Oct 5, 2004 : > Hi List, > > I got a table named foobar with two fields > > last_sms is a 'timestamp without timezone' > resend_interval is a 'integer' > > I choose 'integer' for resend_interval rather than a 'interval' because > the frontend can easier handle the number of seconds. But now I'm stuck > with the query to get all rows that we're older than 'resend_interval' > in seconds. > > My first try was: > > SELECT * >FROM sms_groups >WHERE > (current_timestamp - last_sms) > '3600 seconds'::interval > > This is working great and returns all rows that are older than a hour. > But how I do this with the colum 'resend_interval' in the query? > > SELECT * >FROM sms_groups >WHERE > (current_timestamp - last_sms) > resend_interval::interval > > gives me only a "cannot cast type integer to interval". So how can I > turn an integer to an interval? Or is there an other solution? > Suggestions? If you know for sure that you are keeping resend_interval in seconds, then try as follows: foodb=# SELECT (59::text||' secs')::interval; interval -- 00:00:59 (1 row) foodb=# SELECT (120::text||' secs')::interval; interval -- 00:02:00 (1 row) foodb=# SELECT ((3600*25)::text||' secs')::interval; interval 1 day 01:00:00 (1 row) foodb=# > > P.S: I'm using psql '7.4.5' of gentoo. > > -- -Achilleus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Howto turn an integer into an interval?
try : resend_interval * '1 seconds'::interval this will convert your seconds into an interval. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Stuffing six separate columns into a single array?
> How does dns_ptr relate to other data? Depending on what you're > doing, other ways of organizing your tables might also make sense. These are actually DNS servers authoritive for a domain that is stored in a VARCHAR() in the same table. After sleeping on it, I think using an array is indeed not the right choice. I think splitting the nameservers off into their own table is probably smarter for what I want to do. Thanks, Michael, for the mental boot to the head. :) Benny -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Using timestamp in function
Hi,
I am using PostgreSQL 7.4 and was trying to log the time each part of a
function needs. I found a pretty helpful bit of code in the documentation:
http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html
I used the following function, called inside the another function:
CREATE or replace FUNCTION mylog_test(integer, varchar) RETURNS
timestamp AS '
DECLARE
n ALIAS FOR $1;
logtxt ALIAS FOR $2;
curtime timestamp;
BEGIN
curtime := ''now'';
--INSERT INTO logger VALUES ( nextval(''seq_log''), curtime,
substr(logtxt,0,200));
RAISE NOTICE ''TIME: %'',curtime;
RETURN curtime;
END;
' LANGUAGE plpgsql;
I expected, that the variable curtime gets a new time value, each time
the function is called (at least that is what I understood from the
documentation). This works fine, if I test it with
SELECT mylog_test(5, 'test');
But as soon as I call the funtion from another function (which I need)
the variable curtime does not change anymore.
Can anyone tell me why this does not work and does anyone know a
solution to this?
For test purposes here is a function called test, which does nothing
else than to call mylog_test(..) and spend some time calculating.
CREATE or replace FUNCTION test() RETURNS text AS '
DECLARE
i integer;
j integer;
k integer;
BEGIN
FOR i IN 1..10 LOOP
PERFORM mylog(3, ''val '' || i);
FOR j IN 1..200 LOOP
k:=j;
END LOOP;
END LOOP;
RETURN ''THE END'';
END;
' LANGUAGE 'plpgsql';
SELECT test();
Any help is appreciated
Silke
---(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] Using timestamp in function
Silke Trissl <[EMAIL PROTECTED]> writes: > I expected, that the variable curtime gets a new time value, each time > the function is called (at least that is what I understood from the > documentation). This works fine, if I test it with > SELECT mylog_test(5, 'test'); > But as soon as I call the funtion from another function (which I need) > the variable curtime does not change anymore. "now" refers to the transaction start time. You can get at current time of day with the timeofday() function. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Using timestamp in function
On Tue, Oct 05, 2004 at 05:37:51PM +0200, Silke Trissl wrote:
> CREATE or replace FUNCTION mylog_test(integer, varchar) RETURNS
> timestamp AS '
> DECLARE
> n ALIAS FOR $1;
> logtxt ALIAS FOR $2;
> curtime timestamp;
> BEGIN
> curtime := ''now'';
> --INSERT INTO logger VALUES ( nextval(''seq_log''), curtime,
> substr(logtxt,0,200));
> RAISE NOTICE ''TIME: %'',curtime;
> RETURN curtime;
> END;
> ' LANGUAGE plpgsql;
>
> I expected, that the variable curtime gets a new time value, each time
> the function is called (at least that is what I understood from the
> documentation). This works fine, if I test it with
> SELECT mylog_test(5, 'test');
> But as soon as I call the funtion from another function (which I need)
> the variable curtime does not change anymore.
"Functions and trigger procedures are always executed within a
transaction established by an outer query" [1]
"It is important to know that CURRENT_TIMESTAMP and related functions
return the start time of the current transaction; their values do not
change during the transactiontimeofday() returns the wall-clock
time and does advance during transactions." [2]
[1] http://www.postgresql.org/docs/7.4/static/plpgsql-structure.html
[2]
http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Stored Procedures returning a RECORD
I am attempting to use a stored procedure to pull a report from the database. My questions is if its even possible to pull the data using a function and then treat the returned data as a normal recordset with the web pages. The actual function looks like CREATE OR REPLACE FUNCTION submissionreport(integer, date, text) RETURNS RECORD AS' DECLARE somekey ALIAS for $1; somedate ALIAS for $2; somesortorder ALIAS for $3; submission RECORD; BEGIN SELECT INTO submission (... stuff goes here)ORDER BY sort; RETURN submission;END;' LANGUAGE plpgsql; The error being returned is in the web pages" SQLState: 08S01 Native Error Code: 1 Error while executing the query; ERROR: cannot display a value of type record" Kent Anderson EZYield.com 407-629-0900 www.ezyield.com This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
Re: [SQL] Stored Procedures returning a RECORD
--- Kent Anderson <[EMAIL PROTECTED]> wrote:
> I am attempting to use a stored procedure to pull a
> report from the
> database. My questions is if its even possible to
> pull the data using a
> function and then treat the returned data as a
> normal recordset with the web
> pages.
>
> The actual function looks like
>
> CREATE OR REPLACE FUNCTION submissionreport(integer,
> date, text) RETURNS
> RECORD AS'
Your return value has to be of a specific type
("record" is too generic). Every table automatically
has a type created, which each of its rows conforms
to. Or you could create a custom type, using "create
type".
>
> DECLARE
> somekey ALIAS for $1;
> somedate ALIAS for $2;
> somesortorder ALIAS for $3;
> submission RECORD;
> BEGIN
>
> SELECT INTO submission (... stuff goes here)
> ORDER BY sort;
>
> RETURN submission;
> END;
> ' LANGUAGE plpgsql;
>
> The error being returned is in the web pages"
> SQLState: 08S01 Native Error Code: 1 Error while
> executing the query;
> ERROR: cannot display a value of type record"
>
>
>
> Kent Anderson
> EZYield.com
> 407-629-0900
> www.ezyield.com
>
>
> This electronic message transmission contains
> information from the Company
> that may be proprietary, confidential and/or
> privileged. The information is
> intended only for the use of the individual(s) or
> entity named above. If
> you are not the intended recipient, be aware that
> any disclosure, copying or
> distribution or use of the contents of this
> information is prohibited. If
> you have received this electronic transmission in
> error, please notify the
> sender immediately by replying to the address listed
> in the "From:" field.
>
___
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Regex
Title: Regex I know this is not a regex forum, however I had great assistance last time and thought I would ask, since the regex is looking through an SQL statement. I was trying to parse a SQL statement and get all the tables used. I'm actually doing this in Cold Fusion. I have gone as far as this (FROM | JOIN ).*(?<=INNER JOIN|LEFT JOIN|LEFT OUTER JOIN|AS|WHERE|ORDER BY) I know I need to look for FROM or JOIN and get all text within INNER JOIN,LEFT JOIN,LEFT OUTER JOIN,AS,WHERE,ORDER BY... there are various others. I have tried a forward reference however this does not work, I think it needs to be between the .[^INNER JOIN|LEFT JOIN|LEFT OUT JOIN|AS|WHERE|ORDER BY]* somehow!!! Basically the regex should return TABLEA, TABLEB from: Select * from TABLEA Inner jon TABLEB on tableb.columna = tablea.columna Cheers, Theo __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
[SQL] Database Backup
Hello, Now i am working on Linux database server/Windows Client project. I have a doubbt. How to take DB backup from my windows client machine where DB is intsalled at linux server. pg_dump doen't works from windows client butr its working from local server machine. How to rectify the problem. Then ho w to call pl/pgsql function from VB/.NET. Sreejith ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Regex
Theo Galanakis <[EMAIL PROTECTED]> writes: > Basically the regex should return TABLEA, TABLEB from: > Select * > from TABLEA > Inner jon TABLEB on tableb.columna = tablea.columna You realize of course that this problem is mathematically impossible? Regexes are less powerful than context-free grammars, and so it is a certainty that there exist legal SQL statements that will fool any regex that you invent for this purpose. If you know that the SQL statements are coming from a query generator that produces only a certain style of SQL code, then you might be able to come up with a solution that works reliably for the output of that query generator. But I wonder if you wouldn't be better off bypassing the parse-and-deparse hacking and tapping directly into the query generator. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Regex
Title: RE: [SQL] Regex
Thanks Tom,
I attacked the issue another way which appears to work...
I used :
explain select * from nodes left join node_names on node_names.node_id = nodes.node_id
which returned :
query
QUERY PLAN
1 Merge Right Join (cost=429.16..793.48 rows=4510 width=193)
2 Merge Cond: ("outer".node_id = "inner".node_id)
3 -> Index Scan using node_names_node_id_key on node_names (cost=0.00..278.58 rows= 7253 width=110)
4 -> Sort (cost=429.16..438.89 rows=3894 width=83)
5 Sort Key: nodes.node_id
6 -> Seq Scan on nodes (cost=0.00..196.94 rows=3894 width=83)
and then programatically searched for lines that begin with :
Seq Scan on #table_name#
Index Scan using #indexname# on #table_name#
obtaining the #table_name#
Being : nodes, node_names
Theo
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 6 October 2004 1:36 PM
To: Theo Galanakis
Cc: [EMAIL PROTECTED] Org
Subject: Re: [SQL] Regex
Theo Galanakis <[EMAIL PROTECTED]> writes:
> Basically the regex should return TABLEA, TABLEB from:
> Select *
> from TABLEA
> Inner jon TABLEB on tableb.columna = tablea.columna
You realize of course that this problem is mathematically impossible? Regexes are less powerful than context-free grammars, and so it is a certainty that there exist legal SQL statements that will fool any regex that you invent for this purpose.
If you know that the SQL statements are coming from a query generator that produces only a certain style of SQL code, then you might be able to come up with a solution that works reliably for the output of that query generator. But I wonder if you wouldn't be better off bypassing the parse-and-deparse hacking and tapping directly into the query generator.
regards, tom lane
__This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] Howto turn an integer into an interval?
hello
SELECT ('3600'::int::abstime-'epoch'::abstime)::interval;
try to modify this idea to fit your purpose.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
