Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)
Nemo Terry wrote: > But I must use it in function,so... > Do you have another solution? >>> because obj_name from another table has value like this:S'pore High >>> Polymer. >>> Following error raises: >>> ERROR: syntax error at or near "pore" You'll want to look into the quote_ident() and quote_literal() functions when constructing queries like this. See functions and operators / string functions for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)
On 5/15/07, Nemo Terry <[EMAIL PROTECTED]> wrote: But I must use it in function,so... Do you have another solution? So? t=# CREATE TABLE d(i INT); CREATE TABLE t=# CREATE OR REPLACE FUNCTION add1(x INT) RETURNS INT AS t-# $$ t$# BEGIN t$# INSERT INTO d VALUES(x); t$# RETURN x; t$# END; t$# $$ LANGUAGE plpgsql; CREATE FUNCTION t=# SELECT add1(1); 1 t=# SELECT add1(2); 2 t=# SELECT add1(3); 3 t=# SELECT * FROM d; 1 2 3 Do you need something else? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)
quote_literal() works.Thanks a lot! From: Richard Huxton <[EMAIL PROTECTED]> To: Nemo Terry <[EMAIL PROTECTED]> CC: [email protected] Subject: Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...) Date: Tue, 15 May 2007 08:12:55 +0100 Nemo Terry wrote: > But I must use it in function,so... > Do you have another solution? >>> because obj_name from another table has value like this:S'pore High >>> Polymer. >>> Following error raises: >>> ERROR: syntax error at or near "pore" You'll want to look into the quote_ident() and quote_literal() functions when constructing queries like this. See functions and operators / string functions for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] workday function
Hi folks I need to be able to add and subtract workdays, something like select CURRENT_DATE - '3 work days'::interval; I can't see how to do this natively so I'm looking to write a function to do it and was wondering if anyone's already done it. While Googling I've found that MS Excel has a workday function which seems to do what I want. Any help would be appreciated. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] workday function
Gary Stainburn wrote:
Hi folks
I need to be able to add and subtract workdays, something like
select CURRENT_DATE - '3 work days'::interval;
I can't see how to do this natively so I'm looking to write a function to do
it and was wondering if anyone's already done it.
Don't know of one - not sure what "workday" would mean in a global
sense. I mean, Mon-Fri in most European office settings, but you'd
include Sat in retail settings and in Islamic countries presumably
exclude Fridays. Our local library shuts early on Mondays iirc but is
open Saturday mornings.
Casting to interval won't work because work-days will be a variable
amount of real-days based on what you're adding/subtracting from.
While Googling I've found that MS Excel has a workday function which seems to
do what I want.
Any help would be appreciated.
Well, you'll be wanting to use extract('dow' from current_date) or
similar to figure out how many days to skip. There are national-holiday
resources online, but I'm not sure if they take into account e.g. the
extra day civil servants get in the UK (or used to) for the Queen's
official birthday.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] workday function
Kenneth Gonsalves wrote: On 15-May-07, at 2:39 PM, Richard Huxton wrote: Don't know of one - not sure what "workday" would mean in a global sense. I mean, Mon-Fri in most European office settings, but you'd include Sat in retail settings and in Islamic countries presumably exclude Fridays. Our local library shuts early on Mondays iirc but is open Saturday mornings. and i hear in england people work half day on wednesday Some shops and banks used to when I was a child. Nowadays many supermarkets are open 24 hours (except Sundays - due to legislation) Of course, I've been in companies where some people working for half the day would be a huge improvement :-) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] workday function
On 15-May-07, at 2:39 PM, Richard Huxton wrote: Don't know of one - not sure what "workday" would mean in a global sense. I mean, Mon-Fri in most European office settings, but you'd include Sat in retail settings and in Islamic countries presumably exclude Fridays. Our local library shuts early on Mondays iirc but is open Saturday mornings. and i hear in england people work half day on wednesday -- regards kg http://lawgon.livejournal.com http://nrcfosshelpline.in/web/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] workday function
Kenneth Gonsalves wrote: > > On 15-May-07, at 2:39 PM, Richard Huxton wrote: > >> Don't know of one - not sure what "workday" would mean in a global >> sense. I mean, Mon-Fri in most European office settings, but you'd >> include Sat in retail settings and in Islamic countries presumably >> exclude Fridays. Our local library shuts early on Mondays iirc but is >> open Saturday mornings. > > and i hear in england people work half day on wednesday > We do? First I heard of it! Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] workday function
On Tue, May 15, 2007 at 09:51:34AM +0100, Gary Stainburn wrote: > Hi folks > > I need to be able to add and subtract workdays, something like > > select CURRENT_DATE - '3 work days'::interval; Would that take holidays into account? (and wich ones?) -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] pg_dump?
You want to look at the pg_dump command. A On Mon, May 14, 2007 at 03:48:17PM -0700, Richard Dunne wrote: > PostgreSQL begginer. Whats the best way of exporting(dumping) a > database/table/view/query to a file? > I am using PostgreSQL v 8.2. I am starting postgres server with > C:\WINDOWS\system32\net.exe start pgsql-8.2, (start service) > C:\postgresql-8.2.3-1\bin\psql.exe -h localhost -p 5432 postgres "Richard" > (psql to 'postgres') > both of which are run from shortcuts in the start menu. > my cli starts with "postgres #", then "databasename #" after connecting using > \c. > > Richard. > > > > Get > the free Yahoo! toolbar and rest assured with the added security of spyware > protection. > http://new.toolbar.yahoo.com/toolbar/features/norton/index.php > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] workday function
Hello
You can use functions from Orafce package
http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29#PLVdate
sample:
SELECT plvdate.plvdate.default_holydays('czech');
SELECT plvdate.add_bizdays(CURRENT_DATE, 10);
SELECT plvdate.isbizday('2006-12-25');
Regards
Pavel Stehule
2007/5/15, Gary Stainburn <[EMAIL PROTECTED]>:
Hi folks
I need to be able to add and subtract workdays, something like
select CURRENT_DATE - '3 work days'::interval;
I can't see how to do this natively so I'm looking to write a function to do
it and was wondering if anyone's already done it.
While Googling I've found that MS Excel has a workday function which seems to
do what I want.
Any help would be appreciated.
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Serial number of a record
Hi List, I have one table something like this Name Age = tom 20 ram 25 shyam 30 balram15 hari 10 Now i want serial number of a record. Suppose if i will fire a query "select Name from tablename where age=30". it will give output "shyam" but i also want record no. other then oid, in this case it is 3. is it possible? if yes then how? plz. help me. Thanks Ashok mail2web.com - Microsoft® Exchange solutions from a leading provider - http://link.mail2web.com/Business/Exchange ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Serial number of a record
On Tue, May 15, 2007 at 07:40:25AM -0400, [EMAIL PROTECTED] wrote: > Suppose if i will fire a query "select Name from tablename where age=30". > it will give output "shyam" but i also want record no. other then oid, in > this case it is 3. > > is it possible? > if yes then how? > plz. help me. This depends. Does the record have a serialno field? If not, you can do it with a temporary sequence. But you should be aware that this "serial number" as you call it changes from result to result in that case. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] workday function
On Tuesday 15 May 2007 10:17, Richard Huxton wrote: > Kenneth Gonsalves wrote: > > On 15-May-07, at 2:39 PM, Richard Huxton wrote: > >> Don't know of one - not sure what "workday" would mean in a global > >> sense. I mean, Mon-Fri in most European office settings, but you'd > >> include Sat in retail settings and in Islamic countries presumably > >> exclude Fridays. Our local library shuts early on Mondays iirc but is > >> open Saturday mornings. > > > > and i hear in england people work half day on wednesday > > Some shops and banks used to when I was a child. Nowadays many > supermarkets are open 24 hours (except Sundays - due to legislation) > > Of course, I've been in companies where some people working for half the > day would be a huge improvement :-) As a first stage I've written a number of functions to calculate the standard UK bank holidays, and return these as a dataset. select * from bank_holidays(2007); bank_holidays --- 2007-01-01 2007-04-06 2007-04-09 2007-05-07 2007-05-28 2007-08-27 2007-12-25 2007-12-26 (8 rows) I've attached the code for anyone who want to use or improve it. It should be fairly simple to write a function to use that dataset to ignore *non-working* days. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 create or replace function easter_sunday(year integer) returns date as $$ DECLARE a integer; b integer; c integer; d integer; e integer; month integer; day integer; easter text; BEGIN a := year % 19; b := year % 4; c := year % 7; d := ((a*19)+24) % 30; e := ((b*2)+(c*4)+(d*6)+5) % 7; IF (d+e < 10) THEN month=3; day=d+e+22; ELSE month=4; day=d+e-9; END IF; easter := year || '-' || month || '-' || day; return easter::date; END; $$ LANGUAGE plpgsql; create or replace function new_year(year integer) returns date as $$ DECLARE day integer; wdate text; BEGIN wdate := year || '-01-01'; select into day extract(DOW from wdate::date); IF (day = 0) THEN return (year || '-01-02')::date; END IF; IF (day = 6) THEN return (year || '-01-03')::date; END IF; return (year || '-01-01')::date; END; $$ LANGUAGE plpgsql; create or replace function christmas(year integer) returns date as $$ DECLARE day integer; wdate text; BEGIN wdate := year || '-12-25'; select into day extract(DOW from wdate::date); IF (day = 0) THEN return (year || '-12-26')::date; END IF; IF (day = 6) THEN return (year || '-12-27')::date; END IF; return (year || '-12-25')::date; END; $$ LANGUAGE plpgsql; create or replace function boxing_day(year integer) returns date as $$ DECLARE day integer; wdate text; BEGIN wdate := year || '-12-25'; select into day extract(DOW from wdate::date); IF (day = 0) THEN return (year || '-12-27')::date; END IF; IF (day > 4) THEN return (year || '-12-28')::date; END IF; return (year || '-12-26')::date; END; $$ LANGUAGE plpgsql; create or replace function may_day(year integer) returns date as $$ DECLARE day integer; wdate text; BEGIN wdate := year || '-05-01'; select into day 9-extract(DOW from wdate::date); IF (day > 7) THEN day := day-7; END IF; return (year || '-05-' || day)::date; END; $$ LANGUAGE plpgsql; create or replace function whitsun(year integer) returns date as $$ DECLARE day integer; wdate text; BEGIN wdate := year || '-05-31'; select into day extract(DOW from wdate::date); IF (day = 0) THEN day := 25; ELSE day := 32-day; END IF; return (year || '-05-' || day)::date; END; $$ LANGUAGE plpgsql; create or replace function summer_bank(year integer) returns date as $$ DECLARE day integer; wdate text; BEGIN wdate := year || '-08-31'; select into day extract(DOW from wdate::date); IF (day = 0) THEN day := 25; ELSE day := 32-day; END IF; return (year || '-08-' || day)::date; END; $$ LANGUAGE plpgsql; create or replace function bank_holidays(year integer) returns setof date as $$ DECLARE wdate date; BEGIN select into wdate new_year(year); return next wdate; select into wdate easter_sunday(year)-'2 days'::interval; return next wdate; select into wdate easter_sunday(year)+'1 days'::interval; return next wdate; select into wdate may_day(year); return next wdate; select into wdate whitsun(year); return next wdate; select into wdate summer_bank(year); return next wdate; select into wdate christmas(year); return next wdate; select into wdate boxing_day(year); return next wdate; return; END; $$ LANGUAGE plpgsql; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Adding "on delete cascade" to all foreign key constraints
Hello, I was wondering if there's a fast way I can add "on delete cascade" to all foreign key constraints in my database? Maybe a quick update I can make against the catalog possibly? Or is there a way I can query for all foreign key constrains in the database and then I could write up a quick script to do the updates for me. Any help is appreciated, TIA, Pete Hanson ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Query Validate Records Multiple Tables - Help Needed
On May 15, 8:17 am, Paul251 <[EMAIL PROTECTED]> wrote: > Hello... > > I am trying to validate a asset number (10 Characters) from one table > to another table. Problem is they are in two different DB's and > haven't done that before? > > Basically trying to take record 1 from Table 1/DB1 and validate it > against record 1 in Table 2/DB2 if the record doesn't exist in Table 2/ > DB2 update the flag to N in Table 1/DB1. Seems easy but been > working on it for about a week now no luck :-( I have the mostly > the same colums in both tables but Table 2/DB2 is the host table I > need to verify from > > Does anyone have any help or code to help me do this?? > > Thanks See: http://www.postgresql.org/docs/faqs.FAQ.html#item4.17 https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/dblink/doc/query?rev=26230 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Adding "on delete cascade" to all foreign key constraints
On Tue, May 15, 2007 at 12:19:54PM -0500, Peter Hanson wrote: > Hello, > > I was wondering if there's a fast way I can add "on delete cascade" to all > foreign key constraints in my database? Maybe a quick update I can make > against the catalog possibly? Or is there a way I can query for all foreign > key constrains in the database and then I could write up a quick script to do > the updates for me. You shouldn't go mucking about with the system tables unless absolutely necessary. Instead, write a SELECT that outputs the appropriate syntax. You could do that by querying the catalogs directly, but I think you'll find the pg_user_foreign_keys view defined by http://pgfoundry.org/projects/newsysviews to be most helpful. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
