Re: [SQL] Creating a function with single quotes
On 2009-02-19, Shawn Tayler stay...@washoecounty.us wrote: Hello, This has me befuddled. I am trying create a simple experiment, rather new to SQL and I am running into an issue with single quotes. All I can find on creating a function states the procedure should be contained within single quotes. My problem comes when I want to use a textual representation of an interval. create function csd_interval(integer) returns interval as 'BEGIN RETURN $1 * interval '1 msec' END;' LANGUAGE 'plpgsql'; it always fails at the '1 msec' point. Suggestions? you need to quote the inner quotes, create function csd_interval(integer) returns interval as 'BEGIN RETURN $1 * interval ''1 msec'' END;' LANGUAGE 'plpgsql'; when the function itself uses single quotes in literals this quickly becomes confusing, and so dollar quoting was invented. create function csd_interval(integer) returns interval as $$BEGIN RETURN $1 * interval '1 msec' END;$$ LANGUAGE 'plpgsql'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
Hello Jasen and the List, I tried the $$ quote suggestion: create function f_csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; Here is what I got: edacs=# \i 'f_csd_interval.sql' psql:f_csd_interval.sql:7: ERROR: syntax error at or near END LINE 1: SELECT ( $1 * interval '1 msec') END ^ QUERY: SELECT ( $1 * interval '1 msec') END CONTEXT: SQL statement in PL/PgSQL function f_csd_interval near line2 edacs=# The error at or near END is curious. There must be something wrong in the line before it but I can't see it. Suggestions? In case it matters, the server is v8.2.11 compiled from source on Slackware 11.0 and the terminal is v8.3.6 running on Ubuntu v8.10. On Fri, 2009-02-20 at 08:11 +, Jasen Betts wrote: On 2009-02-19, Shawn Tayler stay...@washoecounty.us wrote: Hello, This has me befuddled. I am trying create a simple experiment, rather new to SQL and I am running into an issue with single quotes. All I can find on creating a function states the procedure should be contained within single quotes. My problem comes when I want to use a textual representation of an interval. create function csd_interval(integer) returns interval as 'BEGIN RETURN $1 * interval '1 msec' END;' LANGUAGE 'plpgsql'; it always fails at the '1 msec' point. Suggestions? you need to quote the inner quotes, create function csd_interval(integer) returns interval as 'BEGIN RETURN $1 * interval ''1 msec'' END;' LANGUAGE 'plpgsql'; when the function itself uses single quotes in literals this quickly becomes confusing, and so dollar quoting was invented. create function csd_interval(integer) returns interval as $$BEGIN RETURN $1 * interval '1 msec' END;$$ LANGUAGE 'plpgsql'; -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
On Friday 20. February 2009, Shawn Tayler wrote: Hello Jasen and the List, I tried the $$ quote suggestion: create function f_csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; Here is what I got: edacs=# \i 'f_csd_interval.sql' psql:f_csd_interval.sql:7: ERROR: syntax error at or near END LINE 1: SELECT ( $1 * interval '1 msec') END ^ QUERY: SELECT ( $1 * interval '1 msec') END CONTEXT: SQL statement in PL/PgSQL function f_csd_interval near line2 edacs=# The error at or near END is curious. There must be something wrong in the line before it but I can't see it. Suggestions? You should place a semicolon at the end of the RETURN line, and remove the one after END, BTW, simple functions as this are better written in the SQL language. I can't speak for the validity of the code itself, but you can rewrite it as create function f_csd_interval(integer) returns interval as $$ SELECT $1 * interval '1 msec' $$ LANGUAGE SQL; -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Fwd: Re: [SQL] Creating a function with single quotes
Memo to self: Remember hit reply all. -- Forwarded Message -- Subject: Re: [SQL] Creating a function with single quotes Date: Friday 20 February 2009 From: Adrian Klaver akla...@comcast.net To: Leif B. Kristensen l...@solumslekt.org On Friday 20 February 2009 6:13:03 am you wrote: On Friday 20. February 2009, Shawn Tayler wrote: Hello Jasen and the List, I tried the $$ quote suggestion: create function f_csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; Here is what I got: edacs=# \i 'f_csd_interval.sql' psql:f_csd_interval.sql:7: ERROR: syntax error at or near END LINE 1: SELECT ( $1 * interval '1 msec') END ^ QUERY: SELECT ( $1 * interval '1 msec') END CONTEXT: SQL statement in PL/PgSQL function f_csd_interval near line2 edacs=# The error at or near END is curious. There must be something wrong in the line before it but I can't see it. Suggestions? You should place a semicolon at the end of the RETURN line, and remove the one after END, BTW, simple functions as this are better written in the SQL language. I can't speak for the validity of the code itself, but you can rewrite it as create function f_csd_interval(integer) returns interval as $$ SELECT $1 * interval '1 msec' $$ LANGUAGE SQL; -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ Actually you need both semicolons. One after the RETURN statement and one after the END statement See below for full details: http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html -- Adrian Klaver akla...@comcast.net --- -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
On Friday 20. February 2009, Adrian Klaver wrote: Actually you need both semicolons. One after the RETURN statement and one after the END statement See below for full details: http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html I see the documentation, but empirically you don't need the semicolon after the END. About twenty years ago I wrote a lot of Turbo Pascal code, and IIRC semicolon after an END was allowed but considered bad style. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How concat 3 strings if 2 are not empty?
Andreas, Kretschmer was quite close, try following: case when trim(coalesce(s1,'')) = '' and trim(coalesce(s3,'')) = '' then '' when trim(coalesce(s1,'')) != '' and trim(coalesce(s2,'')) != '' and trim(coalesce(s3,'')) != '' then s1 || s2 || s3 else trim(coalesce(s1,'')) || trim(coalesce(s3,'')) end Mario Andreas wrote: No. B should only appear if A and C are not empty. B is just a filler. Thanks Andreas A. Kretschmer schrieb: In response to Andreas : I'd like a function that concats 3 string parameters on condition the 1st and 3rd are NOT empty or NULL. xCat (s1, s2, s3) s2 would be a connector that appears only if s1 and s3 are set. NULL and an empty string '' should be handled the same. e.g. 'PostgreSQL', ' is ', ' great'-- 'PostgreSQL is great' NULL, ' is ', ' great' -- 'great' 'PostgreSQL', ' is ', NULL-- 'PostgreSQL' NULL, ' is ', NULL -- NULL 'PostgreSQL', NULL, ' great'-- 'PostgreSQL great' Something like that? test=*# select a,b,c, length(a), length(b), length(c) from string ; a | b | c | length | length | length ++---+++ PostgreSQL | is | great | 10 | 2 | 5 PostgreSQL | is | | 10 | 2 | PostgreSQL || | 10 || | is | || 2 | | is | | 0 | 2 | | is | | 0 | 2 | 0 (6 rows) test=*# test=*# select case when (a is null and c is null) or (a = '' and c = '') then null else coalesce(a,'') || coalesce(b,'')||coalesce(c,'') end from string; case --- PostgreSQLisgreat PostgreSQLis PostgreSQL is (6 rows) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] JOIN and aggregate problem
I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C combinations in T2 where X = true. There might be no matching pair in T2 or there might be match but X is false. How can I express this? -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
I might be missing something but does this solve your issue? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE); SELECT t1.d, t1.s, t1.c, t2.x, COUNT(*) FROM t1 INNER JOIN t2 ON t1.d = t2.d AND t1.s = t2.s AND t1.c = t2.c WHERE t2.x = 'FALSE' GROUP BY t1.d, t1.s, t1.c, t2.x; On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz postgre...@numerixtechnology.de wrote: I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C combinations in T2 where X = true. There might be no matching pair in T2 or there might be match but X is false. How can I express this? -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
Tarlika Elisabeth Schmitz wrote: I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C combinations in T2 where X = true. There might be no matching pair in T2 or there might be match but X is false. try something like: SELECT t1.d, t1.s, t1.c, count(*) FROM t1 LEFT JOIN ( SELECT d,s,c FROM t2 WHERE x ) AS t2_true USING (d,s,c) GROUP BY t1.d, t1.s, t1.c; Warning - not tested -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
On Fri, 20 Feb 2009, Tarlika Elisabeth Schmitz wrote: I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C combinations in T2 where X = true. There might be no matching pair in T2 or there might be match but X is false. How can I express this? Maybe something like one of these barely tested queries? select d, s, c, sum(case when t2.x then 1 else 0 end) from t1 left outer join t2 using(d,s,c) group by d, s, c; or select d,s,c, (select count(*) from t2 where t2.d=t1.d and t2.s=t1.s and t2.c=t1.c and t2.x) from t1; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
Scratch this one won't work for you. On Fri, Feb 20, 2009 at 1:03 PM, Bob Henkel bob.hen...@gmail.com wrote: I might be missing something but does this solve your issue? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE); SELECT t1.d, t1.s, t1.c, t2.x, COUNT(*) FROM t1 INNER JOIN t2 ON t1.d = t2.d AND t1.s = t2.s AND t1.c = t2.c WHERE t2.x = 'FALSE' GROUP BY t1.d, t1.s, t1.c, t2.x; On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz postgre...@numerixtechnology.de wrote: I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C combinations in T2 where X = true. There might be no matching pair in T2 or there might be match but X is false. How can I express this? -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
How about this? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE),(5,5,5,TRUE),(5,5,5,TRUE); SELECT t1.d, t1.s, t1.c, CASE WHEN t2.x IS NULL THEN 0 ELSE COUNT(*) END FROM t1 LEFT OUTER JOIN t2 ON t1.d = t2.d AND t1.s = t2.s AND t1.c = t2.c AND t2.x = TRUE GROUP BY t1.d, t1.s, t1.c,t2.x; --DROP TABLE t1; --DROP TABLE t2; On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz postgre...@numerixtechnology.de wrote: I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C combinations in T2 where X = true. There might be no matching pair in T2 or there might be match but X is false. How can I express this? -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
