Re: [SQL] array_to_string
"Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Sabin Coanda" <[EMAIL PROTECTED]> writes: >> I used the function array_to_string, and I found it ignores NULL values, >> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'. > > Do you have a better idea? > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > >http://www.postgresql.org/about/donate > I found the function would be useful to build dynamic statements that includes array items. For instance I have a function with an array argument of integer. In that function I'd like to send that argument by a dynamic string to other function. If array_to_string would return the whole elements, including NULLs, I'd be able to build that statement. But with the present behavior, the result statement damages the array structure. On the other hand, I saw there is available its complement function string_to_array. It would be nice to be able to reverse a string built by array_to_string, with string_to_array, getting the original array. This could happen if the function would consider the NULLs as well. A simple 'NULL' string would be enough to fulfil this reversing process, so that array_to_string( ARRAY[1,NULL,3], ',' ) would returns '1,NULL,3'. A problem will occur when it would be applied to string arrays, because NULL string, and 'NULL' string value would have the same result. This could be solved if the string arrays would be formed with the same rules as for SQL constant syntax. I mean with quotes. So that, array_to_string( ARRAY[ 'a', 'NULL', NULL, 'b'], '/' ) would return not a/NULL/NULL/b , but 'a'/'NULL'/NULL/'b'. Consequently, string_to_array function should interpret this result. Regards, Sabin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Exec a text variable as select
Hello, I'm creating a function that will create a select statement into a while, this select will be stored into a text variable, after while ends I need to execute this query stored into variable, on SQLSERVER I can do: EXEC(text_variable) How can I do this on Postgres? I appreciate any help Thanks ---(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] [GENERAL] Exec a text variable as select
am Mon, dem 18.06.2007, um 10:14:32 -0300 mailte Ranieri Mazili folgendes: > Hello, > > I'm creating a function that will create a select statement into a > while, this select will be stored into a text variable, after while ends > I need to execute this query stored into variable, on SQLSERVER I can do: > EXEC(text_variable) > How can I do this on Postgres? With EXECUTE. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Setting variable
Hello, I need to know why can't I do it? CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; The last line (END_DATE := START_DATE - interval '3 year';) generate the following error: ERROR: operator is not unique: "unknown" / "unknown" SQL state: 42725 Hint: Could not choose a best candidate operator. You may need to add explicit type casts. Context: PL/pgSQL function "lost_hours_temp" line 10 at assignment How can I solve it? Thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Setting Variable - (Correct)
Hello, I'm trying do the following function: CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM END_DATE)+3 LOOP QUERY := 'SELECTCAST(EXTRACT(YEAR FROM A.production_date) || '/' || EXTRACT(MONTH FROM A.production_date) AS TEXT) as date, SUM(production_hours) AS production_hours, B.id_production_area FROM production A, product B WHERE EXTRACT(MONTH FROM production_date) = EXTRACT(MONTH FROM ' || START_DATE || ') AND EXTRACT(YEAR FROM A.production_date) = EXTRACT(YEAR FROM ' || START_DATE || ') AND lost_hours = ' || 'S' ||' AND A.id_product = B.id_product GROUP BY id_production_area, date'; START_DATE := START_DATE - interval '1 month'; END LOOP; RETURN QUERY; END; $BODY$ LANGUAGE 'plpgsql'; My problem is into WHILE, I'm trying to concatenate variables with the string, but I guess that it's generating an error. What's the correct form to concatenate strings with query in my case? Thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Setting variable
On Jun 18, 2007, at 9:29 , Ranieri Mazili wrote: CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; The last line (END_DATE := START_DATE - interval '3 year';) generate the following error: ERROR: operator is not unique: "unknown" / "unknown" SQL state: 42725 Hint: Could not choose a best candidate operator. You may need to add explicit type casts. Context: PL/pgSQL function "lost_hours_temp" line 10 at assignment Note that the error is at line 10. You've only shown lines 1 through 7 of the function body, so you haven't actually shown us where the error is. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [GENERAL] Setting Variable - (Correct)
On Jun 18, 2007, at 9:34 , Ranieri Mazili wrote:
Hello, I'm trying do the following function:
CREATE OR REPLACE FUNCTION lost_hours_temp(date)
RETURNS text AS
$BODY$
DECLARE
START_DATE date;
END_DATE date;
QUERY text;
BEGIN
START_DATE := $1;
END_DATE := START_DATE - interval '3 year';
WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM
END_DATE)+3 LOOP
QUERY := 'SELECTCAST(EXTRACT(YEAR FROM
A.production_date) || '/' || EXTRACT(MONTH FROM A.production_date)
AS TEXT) as date,
SUM(production_hours) AS production_hours,
B.id_production_area
FROM production A, product B
WHERE EXTRACT(MONTH FROM production_date) = EXTRACT
(MONTH FROM ' || START_DATE || ')
AND EXTRACT(YEAR FROM A.production_date) = EXTRACT
(YEAR FROM ' || START_DATE || ')
AND lost_hours = ' || 'S' ||'
AND A.id_product = B.id_product
GROUP BY id_production_area, date';
START_DATE := START_DATE - interval '1 month';
END LOOP;
RETURN QUERY;
END;
$BODY$
LANGUAGE 'plpgsql';
My problem is into WHILE, I'm trying to concatenate variables with
the string, but I guess that it's generating an error.
It looks like you've got a number of problems here, but overall it
appears you're approaching this from the wrong way. What's the final
result you want? I doubt it's just a query string. You probably want
to run this query somewhere, and you can do this from within PL/
pgSQL. You may want to look up set returning functions.
Looking over your function, I'm a little confused about what you're
trying to do. I'm guessing the (final) result you're trying to get is
the number of hours lost for each product per area per month for the
three years prior to the provided date. You should be able to do this
in just a single SQL query, something like:
SELECT date_trunc('month', production.production_date)::date
AS production_period
, product.id_production_area
, sum(production_hours) as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
AND date_trunc('month', a.production_date)::date BETWEEN
date_trunc('month', ? - 3 * interval '1 year')::date
AND date_trunc('month', ?)::date;
Things that were puzzling to me about your code:
* START_DATE is *after* END_DATE (END_DATE := START_DATE -
INTERVAL '3 year')
* WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM
END_DATE)+3 LOOP will only be true for a limited number of months,
not over the whole three-year range. The idea of three years has no
real meaning in the query after this point.
Anyway, hope this helps.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] Setting Variable - (Correct)
On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote:
Looking over your function, I'm a little confused about what you're
trying to do. I'm guessing the (final) result you're trying to get
is the number of hours lost for each product per area per month for
the three years prior to the provided date.
Or, rather, the number of hours lost per production area per month
for the three years prior to the provided date.
SELECT date_trunc('month', production.production_date)::date
AS production_period
, product.id_production_area
, sum(production_hours) as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
AND date_trunc('month', a.production_date)::date BETWEEN
date_trunc('month', ? - 3 * interval '1 year')::date
AND date_trunc('month', ?)::date;
Looks like I forgot the GROUP BY clause:
GROUP BY production_period, id_production_area
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] Setting Variable - (Correct)
Original Message
Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct)
From: Michael Glaesemann <[EMAIL PROTECTED]>
To: Michael Glaesemann <[EMAIL PROTECTED]>
Date: 18/6/2007 13:15
On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote:
Looking over your function, I'm a little confused about what you're
trying to do. I'm guessing the (final) result you're trying to get is
the number of hours lost for each product per area per month for the
three years prior to the provided date.
Or, rather, the number of hours lost per production area per month for
the three years prior to the provided date.
SELECT date_trunc('month', production.production_date)::date
AS production_period
, product.id_production_area
, sum(production_hours) as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
AND date_trunc('month', a.production_date)::date BETWEEN
date_trunc('month', ? - 3 * interval '1 year')::date
AND date_trunc('month', ?)::date;
Looks like I forgot the GROUP BY clause:
GROUP BY production_period, id_production_area
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Thanks a lot for your prompt reply.
You query is perfect for my problem, but I need another thing with it, I
need to return the sum of production_hours of each month of the current
year, and I need to return too the average of the 3 past years, can I do
all in only one query or I need to do a UNION with another query?
More one time, thanks a lot for your help.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] Setting Variable - (Correct)
[Please reply to the list so that others may benefit from and participate in the discussion.] On Jun 18, 2007, at 11:32 , Ranieri Mazili wrote: Thanks a lot for your prompt reply. You query is perfect for my problem, but I need another thing with it, I need to return the sum of production_hours of each month of the current year, and I need to return too the average of the 3 past years, can I do all in only one query or I need to do a UNION with another query? Glad you found it helpful. What have you tried so far? Michael Glaesemann grzm seespotcode net ---(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] [GENERAL] Setting Variable - (Correct)
Original Message
Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct)
From: Michael Glaesemann <[EMAIL PROTECTED]>
To: Ranieri Mazili <[EMAIL PROTECTED]>
Date: 18/6/2007 13:50
[Please reply to the list so that others may benefit from and
participate in the discussion.]
On Jun 18, 2007, at 11:32 , Ranieri Mazili wrote:
Thanks a lot for your prompt reply.
You query is perfect for my problem, but I need another thing with
it, I need to return the sum of production_hours of each month of the
current year, and I need to return too the average of the 3 past
years, can I do all in only one query or I need to do a UNION with
another query?
Glad you found it helpful. What have you tried so far?
Michael Glaesemann
grzm seespotcode net
---(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
Look how I did:
SELECT date_trunc('month', production.production_date)::date
AS production_period
, product.id_production_area
, sum(production_hours) as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
AND date_trunc('month', production.production_date)::date BETWEEN
date_trunc('month', CAST('2007-06-18' AS date) - (EXTRACT(MONTH
FROM CAST('2007-06-18' AS date))-1) * interval '1 month')::date
AND date_trunc('month', CAST('2007-06-18' AS date))::date
GROUP BY production_period, id_production_area
UNION
SELECT date_trunc('year', production.production_date)::date
AS production_period
, product.id_production_area
, sum(production_hours)/12 as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
AND date_trunc('year', production.production_date)::date BETWEEN
date_trunc('year', CAST('2007-06-18' AS date) - 3 * interval '1
year')::date
AND date_trunc('year', CAST('2007-06-18' AS date) - 1 * interval
'1 year')::date
GROUP BY production_period, id_production_area
ORDER BY production_period DESC
I changed the "?" for values to test.
Look, I did a UNION, exist other way to do it better?
Thanks
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] Setting Variable - (Correct)
On Jun 18, 2007, at 12:11 , Ranieri Mazili wrote:
Look, I did a UNION, exist other way to do it better?
Considering your aggregates are different, you shouldn't really union
them. In the upper query of the union, you've got production_period
(which is actually a date that represents the beginning of a month-
long period), id_production_area, and an aggregate using sum as
total_production_hours. In the lower query of the union, you've got
production_period (a date representing the beginning of a year-long
period), id_production_area, and a aggregate representing monthly
average hours as total_production_hours. These are logically two
separate results, and should not be unioned. It's easier to see if
the columns are renamed appropriately:
SELECT production_month, id_production_area, monthly_production_hours
...
UNION
SELECT production_year, id_production_area,
monthly_average_production_hours
...
You can see that they're different. One consequence of this is that
for the query you have, you'll have more than on column with a date
'-01-01': is this a production_month or a production_year?
I guess I'd split it into two queries (and rename the columns). You
might also be able to join the to queries so you get a result
something like
SELECT production_year
, production_month
, id_production_area
, monthly_production_hours
, monthly_average_production_hours
Each month for the entire three-year range would be listed, and the
production_year and monthly_production_hours would be repeated for
each month of the year.
Yet another way to do it would be to create a view for
production_month, id_production_area, and monthly_production_hours
(with no restriction on date range), and then call the view twice:
once for the monthly figures for a year:
SELECT production_month, id_production_area, monthly_production_hours
FROM monthly_production
WHERE production_month BETWEEN date_trunc('month', ? - interval '1
year') AND date_trunc('month', ?);
and once more for the yearly figures for the past three:
SELECT date_trunc('year', production_month) as production_year
, sum(production_month) as number_of_months -- so you can see if you
have a full twelve-months
, id_production_area
, average(monthly_production_hours)
FROM monthly_production
WHERE date_trunc('year', production_month)
GROUP BY -- left as an exercise for the reader :)
Note that if you don't have any lost hours for a given year, you may
have some surprising results. You might want to look at
generate_series or some other solution for generating a full list of
months for you to join against.
By the way, if you're going to do a lot of the same date_trunc work,
you might want to create some functions that do this for you, e.g.
(untested),
CREATE FUNCTION trunc_year(date)
RETURNS date
IMMUTABLE
LANGUAGE sql AS $_$
select date_trunc('year', $1)::date
$_$;
CREATE FUNCTION truc_years_ago(date, integer)
RETURNS date
IMMUTABLE
LANGUAGE sql AS $_$
select date_trunc('year', $1 - $2 * INTERVAL '1 year')::date
$_$:
Note that foo::date is PostgreSQL-specific for CAST(foo AS DATE).
Anyway, hope that gives you something to think about.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] tsearch2() trigger and domain types...
Okay, so I have an interesting problem that I'm having a hard time figuring out. For standardization in my database I use a domain (login_t) for my login column in my profile table. I'm trying to use the tsearch2() trigger to index several columns, including the login column, into a column called "search_index." So I added the trigger as attached with the hope that it would do just that. Well, here's what I get upon every update and insert to the profile table: WARNING: TSearch: 'login' is not of character type I've tried casting and such, but nothing seems to work. Anybody got any ideas? Thanks in advance. -- ~ Michael D. Stemle, Jr. <>< (A)bort, (R)etry, (I)nfluence with large hammer 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html CREATE TRIGGER "tgr_profile_search_index" AFTER INSERT OR UPDATE ON "public"."profile" FOR EACH ROW EXECUTE PROCEDURE "public"."tsearch2"(search_index, description, interests, login, hometown, email, url, im_names, name); COMMENT ON TRIGGER "tgr_profile_search_index" ON "public"."profile" IS 'This trigger will keep the search index up to date for the profile table. This index is based on the description and the interests columns.'; ---(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] tsearch2() trigger and domain types...
"Michael D. Stemle, Jr." <[EMAIL PROTECTED]> writes: > For standardization in my database I use a domain (login_t) for my login > column in my profile table. > Well, here's what I get upon every update and insert to the profile table: > WARNING: TSearch: 'login' is not of character type The tsearch trigger seems to insist that the column be text, varchar(n), or char(n) ... no domains need apply :-( I'm not real sure why it doesn't just invoke the column's output function and be datatype-agnostic. Or at least do that when the shortcut "I know what text looks like" path isn't applicable. regards, tom lane ---(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] How can you generate a counter for ordered sets?
On May 17, 8:19 am, [EMAIL PROTECTED] (Christopher Maier) wrote: > Conceptually, all the exons for a given gene form a set, ordered by > their "start" attribute. I need to add a new integer column to the > table to store a counter for each exon that indicates their position > in this ordering. > > Is there a straightforward way to populate this new position column? > I've done an iterative solution in PL/pgSQL which works (slowly), but > I was wondering if there was a more efficient way to do this kind of > thing. SELECT * FROM EXON; id | gene | start | stop +--+---+-- 1 |1 | 1 | 10 2 |2 |11 | 20 3 |3 |21 | 30 SELECT ID, GENE, START, STOP , GENERATE_SERIES(START, STOP) AS POSITION FROM EXON; id | gene | start | stop | position +--+---+--+-- 1 |1 | 1 | 10 |1 1 |1 | 1 | 10 |2 1 |1 | 1 | 10 |3 1 |1 | 1 | 10 |4 1 |1 | 1 | 10 |5 1 |1 | 1 | 10 |6 1 |1 | 1 | 10 |7 1 |1 | 1 | 10 |8 1 |1 | 1 | 10 |9 1 |1 | 1 | 10 | 10 2 |2 |11 | 20 | 11 2 |2 |11 | 20 | 12 2 |2 |11 | 20 | 13 2 |2 |11 | 20 | 14 2 |2 |11 | 20 | 15 2 |2 |11 | 20 | 16 2 |2 |11 | 20 | 17 2 |2 |11 | 20 | 18 2 |2 |11 | 20 | 19 2 |2 |11 | 20 | 20 3 |3 |21 | 30 | 21 3 |3 |21 | 30 | 22 3 |3 |21 | 30 | 23 3 |3 |21 | 30 | 24 3 |3 |21 | 30 | 25 3 |3 |21 | 30 | 26 3 |3 |21 | 30 | 27 3 |3 |21 | 30 | 28 3 |3 |21 | 30 | 29 3 |3 |21 | 30 | 30 ---(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
