[SQL] Using function like where clause
Hello, I have 2 questions. 1) Can I use a function that will return a string in a where clause like bellow? select * from table where my_function_making_where() and another_field = 'another_think' 2) Can I use a function that will return a string to return the list of columns that I want to show like below? select my_function_making_list_of_columns() from table where field_test = 'mydatum' Thanks ---(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
[SQL] Restricting columns by users
Hello, I'm looking for a method to restrict columns by users on postgresql, searching in google I found what I want, but in mysql, below I reproduce the paragraph that shows what I exactly want: "MySQL can also restrict access on the table level and even on the column level. What this means is that a user can have zero privileges on a database, but can have all privileges on a table in that database. Alternatively, a user can have zero privileges on a database, restricted privileges on a table, and all privileges on any particular column on that table. This is done by inserting records into the TABLE_PRIV table and the COLUMN_PRIV table." Exist something like it for postgresql? Thanks ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Using function like where clause
Hello, I have 2 questions. 1) Can I use a function that will return a string in a where clause like bellow? select * from table where my_function_making_where() and another_field = 'another_think' 2) Can I use a function that will return a string to return the list of columns that I want to show like below? select my_function_making_list_of_columns() from table where field_test = 'mydatum' Thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Tunning PostgreSQL performance for views on Windows
Hello, I'm developing a BI and as database it's using postgresql 8.2, how data are very detailed, I'm creating a view to consolidate the most important data, but the performance of view is very poor, 1 minute to perform more or less without where clause. I need to know how I can increase the performance, if exist some option to do cache, because the view will change only one time per day. My configuration is default, without modifications after install. I'm using windows 2003 server with a dell server with 4GB of memory. To create the view, I created some functions, and then perform they on one select like: select A.field1, B.field2, ... from function_A() A, function_B() B... Is this the best way to do it? I appreciate any help. Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [GENERAL] Working with dates
Original Message
Subject: [GENERAL] Working with dates
From: Ranieri Mazili <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Date: 5/7/2007 16:00
Hello,
I need to do the following select:
Number of days of month - weekends - holydays
So this query will return the number of days that people can work
Look that I have the holydays in one table, it's bellow:
CREATE TABLE holidays
(
id_holidays serial NOT NULL,
dt_holiday date,
holiday_description character varying(60),
input_date timestamp without time zone NOT NULL,
CONSTRAINT holidays_pkey PRIMARY KEY (id_holidays)
)
I have no idea of how do it
If someone could help, I would appreciate.
Thanks
---(end of broadcast)---
TIP 6: explain analyze is your friend
I can.
select count(A.data)
from (select (date_trunc('month',(select production_date from production
order by production_date desc limit 1)::date)::date + x * '1
day'::interval)::date as data from generate_series(0,31) x) A
where extract(month from A.data) = extract(month from (select
production_date from production order by production_date desc limit
1)::date )
and extract(dow from A.data) <> 0
and extract(dow from A.data) <> 6
and A.data not in (select dt_holiday from holidays)
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] Working with dates
Hello, I need to do the following select: Number of days of month - weekends - holydays So this query will return the number of days that people can work Look that I have the holydays in one table, it's bellow: CREATE TABLE holidays ( id_holidays serial NOT NULL, dt_holiday date, holiday_description character varying(60), input_date timestamp without time zone NOT NULL, CONSTRAINT holidays_pkey PRIMARY KEY (id_holidays) ) I have no idea of how do it If someone could help, I would appreciate. Thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [GENERAL] Subquery problems
Original Message
Subject: Re:[GENERAL] Subquery problems
From: Merlin Moncure <[EMAIL PROTECTED]>
To: Ranieri Mazili <[EMAIL PROTECTED]>
Date: 19/6/2007 10:40
On 6/19/07, Ranieri Mazili <[EMAIL PROTECTED]> wrote:
Hello,
I'm having another "problem", I have a function that declares 12
variable,
one per month and each them execute a select like bellow:
DIV_MES01 := (select count(distinct production_date) from production
where
extract(month from production_date) = '01' and extract(year from
production_date) = EXTRACT(YEAR FROM current_date));
Then, I need to check if the variable is equal 0:
IF DIV_MES01 = 0 THEN
DIV_MES01 := 1;
END IF;
Finally, I perform the following query:
SELECTcast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast('01' as text) AS mes
FROM head_count A, machine B, machine_type C
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = '01'
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type
Doing it, I need to perform 12 querys united by "UNION", what I want
to do
is unify it in only one query, I tryed with the query bellow:
SELECT date_trunc('month', A.head_count_date)::date as head_date,
cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/(select count(distinct
production_date)
from production
whereextract(month from
production_date) = EXTRACT(MONTH FROM date_trunc('month',
A.head_count_date)::date)
and extract(year from
production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
C.id_production_area
FROM head_count A, machine B, machine_type C
WHEREdate_trunc('month', A.head_count_date)::date BETWEEN
date_trunc('month', current_date - (EXTRACT(MONTH FROM
current_date)-1) * interval '1 month')::date
AND date_trunc('month', current_date)::date
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type,
head_count_date,head_date
ORDER BY id_production_area, head_count_date,head_date DESC
But the results aren't what I want.
What I trying to do is possible?
I appreciate any help.
Thanks
sure!.
SELECTcast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast(DIV_MES01 as text) AS mes
FROM head_count A, machine B, machine_type C,
(
select case when ct = 0 then 1 else ct end as DIV_MES01 from
(
select count(distinct production_date) as ctfrom production where
extract(year from production_date) = EXTRACT(YEAR FROM current_date)
) q
) D
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type;
ok, I didn't syntax check this monster, but it should give you a
start...the trick is to use an 'inline view' to expand your variable
list into a set.
merlin
---(end of broadcast)---
TIP 6: explain analyze is your friend
On this way didn't work, I wanna do only one query to return the data of
entire year, not only one month, but thanks for try.
If someone have an idea of how do it, please, help :D
Thanks
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Subquery problems
Hello,
I'm having another "problem", I have a function that declares 12
variable, one per month and each them execute a select like bellow:
*DIV_MES01 := (select count(distinct production_date) from production
where extract(month from production_date) = '01' and extract(year from
production_date) = EXTRACT(YEAR FROM current_date));
*Then, I need to check if the variable is equal 0:
*IF DIV_MES01 = 0 THEN
DIV_MES01 := 1;
END IF;
*Finally, I perform the following query:
*SELECTcast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast('01' as text) AS mes
FROM head_count A, machine B, machine_type C
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = '01'
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type
*Doing it, I need to perform 12 querys united by "UNION", what I want to
do is unify it in only one query, I tryed with the query bellow:
*SELECT date_trunc('month', A.head_count_date)::date as head_date,
cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/(select count(distinct production_date)
from production
whereextract(month from
production_date) = EXTRACT(MONTH FROM date_trunc('month',
A.head_count_date)::date)
and extract(year from
production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
C.id_production_area
FROM head_count A, machine B, machine_type C
WHEREdate_trunc('month', A.head_count_date)::date BETWEEN
date_trunc('month', current_date - (EXTRACT(MONTH FROM
current_date)-1) * interval '1 month')::date
AND date_trunc('month', current_date)::date
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type, head_count_date,head_date
ORDER BY id_production_area, head_count_date,head_date DESC
*But the results aren't what I want.
What I trying to do is possible?
I appreciate any help.
Thanks
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)
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
[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
[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] 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
[SQL] CREATE RULE with WHERE clause
Hello, I need to create a rule, but I need that it have a WHERE clause, how bellow: CREATE RULE rule_role_sul AS ON SELECT TO t1 WHERE roles = 'role_sul' DO INSTEAD SELECT field1, field2 FROM t2; CREATE RULE rule_role_sul AS ON SELECT TO t1 WHERE roles = 'role_norte' DO INSTEAD SELECT field3, field4 FROM t2; Someone knows how can I do it? I appreciate any help Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Inserting a path into Database
Hello,
I need to insert a path into a table, but because "\" I have a error by
postgres, so how can I insert a path like bellow into a table:
insert into production values ('C:\Program Files\My program');
I appreciate any help
Thanks
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Encrypted column
Hello, I need to store users and passwords on a table and I want to store it encrypted, but I don't found documentation about it, how can I create a table with columns "user" and "password" with column "password" encrypted and how can I check if "user" and "password" are correct using a sql query ? I appreciate any help Thanks Ranieri Mazili ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Jumping Weekends
Hello, (sorry for my poor english) It's my first post here, and my doubt is very simple (I guess). I have a function to populate a table, into "WHILE" I have the follow piece of code: --Jump Weekend IF (SELECT TO_CHAR(CAST(PRODUCTION_DATE as date),'Day')) = 'Saturday' THEN PRODUCTION_DATE := PRODUCTION_DATE + 2; END IF; It's to jump weekend adding 2 days on the variable "PRODUCTION_DATE", the type of the variable is "DATE". But, I don't know why, it's doesn't work properly, it's doesn't jump weekends, it's continues normally, someone knows what's happen or what am I doing wrong? I appreciate any help. Thanks Ranieri Mazili ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
