Re: [SQL] Creating a function with single quotes
On 2009-02-19, Shawn Tayler 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 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
Re: [SQL] Creating a function with single quotes
Hi Leif! Thank you to you and the group. Worked like a charm. The SQL language was the key Shawn On Fri, 2009-02-20 at 15:12 +0100, Leif B. Kristensen wrote: > 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; -- 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
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 To: "Leif B. Kristensen" 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: Fwd: Re: [SQL] Creating a function with single quotes
Ah.. Missed that one. Thank you Adrian! Shawn On Fri, 2009-02-20 at 06:27 -0800, 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 -- 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 6:29:43 am Leif B. Kristensen wrote: > 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. Learned something new. I went and reread the docs and found: "Each declaration and each statement within a block is terminated by a semicolon. A block that appears within another block must have a semicolon after END, as shown above; however the final END that concludes a function body does not require a semicolon." I have always seen them terminated with a semicolon and did that assuming thing. -- 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: >On Friday 20 February 2009 6:29:43 am Leif B. Kristensen wrote: >> About twenty years ago I wrote a lot of Turbo Pascal code, and IIRC >> semicolon after an END was allowed but considered bad style. The rules concerning ENDs and semicolons in Pascal were quite more complex than that. I won't try to make a synopsis here as it's massively off-topic. These days I prefer languages with curly braces as block delimiters; however the old Pascal exposure does come back when I write sprocs in Postgres. Actually, I believe that the syntax is derived from ALGOL, the grandmother of all structured languages, rather than Pascal. >Learned something new. I went and reread the docs and found: > >"Each declaration and each statement within a block is terminated by a >semicolon. A block that appears within another block must have a > semicolon after END, as shown above; however the final END that > concludes a function body does not require a semicolon." > >I have always seen them terminated with a semicolon and did that > assuming thing. «Assumption is the mother of all fuckups» :-) IMHO, someone should revise the syntax of the example in the document (http://www.postgresql.org/docs/current/interactive/plpgsql-structure.html) to state that the semicolon after the final END is not required. -- 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 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 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 > 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 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
