Re: [SQL] Problem in age on a dates interval

[Luis Sousa]

Yes, that's a much more clever solution than the one I used.
Thanks
Best regards,
Luis Sousa
Alexander M. Pravking wrote:
On Mon, Jul 19, 2004 at 10:00:50AM +0100, Luis Sousa wrote:
 

I worked around this problem returning the difference between the two 
dates, using extract doy from both.
Anyway, this will cause a bug on my code when changing the year. Any ideas?
   

Why don't you use the minus operator?
SELECT '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp;
?column?
--
86 days
Or, if you need the age just in days:
SELECT extract(day from '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp);
date_part
---
   86
or
SELECT '2004-05-14 16:00'::date - '2004-02-18 16:00'::date;
?column?
--
  86
Note that '2004-05-14 16:00'::date is actually '2004-05-14 00:00'::date,
so the last two are not always equal.
 

Tom Lane wrote:
   

Theodore Petrosky <[EMAIL PROTECTED]> writes:
 

wow at first I thought I had my head around a leap
year problem so I advanced your query a year
 

   

I think what's going on here is a difference of interpretation about
whether an "M months D days" interval means to add the months first
or the days first.  For instance
2005-02-18 plus 2 months = 2005-04-18, plus 24 days = 2005-05-12
2005-02-18 plus 24 days = 2005-03-14, plus 2 months = 2005-05-14
The timestamp-plus-interval operator is evidently doing addition the
first way, but it looks like age() is calculating the difference in a
way that implicitly corresponds to the second way.
I have some vague recollection that this has come up before, but
I don't recall whether we concluded that age() needs to be changed
or not.  In any case it's not risen to the top of anyone's to-do list,
because I see that age() still acts this way in CVS tip.
			regards, tom lane
 

 

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] surrogate key or not?

[Markus Bertheau]

Hi,

I have a database that has types in them with unique names. These types
are referenced from other tables through a surrogate integer key. I'm
now wondering if I should eliminate that surrogate key and just use the
name as the primary key. Afaiu, surrogate keys are primarily there to
make joining tables or otherwise searching for a record faster, because
it's faster to compare two integers than it is to compare two strings.

Now when I want to search for a type in types or another table that
references types(type_id), under what circumstances is it advisable to
have a surrogate integer key and not use the unique type name? Is
searching for an integer as fast as is searching for a string when both
have an index? How many records in the type table do I need to make a
surrogate key a not unsignificantly faster way to retrieve a row? What
about joins? Are these the right questions?

Thanks.

-- 
Markus Bertheau <[EMAIL PROTECTED]>


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Stored procedures and "pseudo" fields..

[Lars Erik Thorsplass]

I also posted this to the general list, which might not have been a
suitable forum.

--
I have recently ventured into the exciting world of stored procedures,
but I have now become lost.

Background:

Am currently working on access control in a web application. My goal
is to process access control on the SQL level. This way if a row is in
the result set, you have access to it, if not, you don't.

Problem:

My stored procedure "acl_check()" takes two integers as parameters.
Param1 is the object id to check acl on, Param 2 is the object id of
the user currently using the system. The procedure returns a positive
number (1 or 3 ) if you have some kind of access to the object. As one
might understand I want the returned value from the acl_check()
procedure to be a part of the result set.

Kinda like this:

SELECT *, acl_check( objects.obid,  ) AS mode FROM objects
WHERE mode > 0;

This gives me a: ERROR:  column "mode" does not exist

If I remove the "mode > 0" logic, I get a result set with mode in it as
expected. Why cant I do logic tests with the mode "field" ?

I tried a different approach with a different error:

SELECT * FROM objects, acl_check( objects.obid, 32 ) as mode WHERE mode > 0;

This gives me a: ERROR:  function expression in FROM may not refer to
other relations of same query level.

Here objecs.obid is unknown i suppose, but if I enter "10" as the
first param using the mode "field" in a logic statement works.

I would appreciate any hints to a workaround which would enable me to
accomplish my scenario.

Best regards,

L.E.Thorsplass

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Stored procedures and "pseudo" fields..

[Tomasz Myrta]

On 2004-07-20 15:34, UÅytkownik Lars Erik Thorsplass napisaÅ:
My stored procedure "acl_check()" takes two integers as parameters.
Param1 is the object id to check acl on, Param 2 is the object id of
the user currently using the system. The procedure returns a positive
number (1 or 3 ) if you have some kind of access to the object. As one
might understand I want the returned value from the acl_check()
procedure to be a part of the result set.
Kinda like this:
SELECT *, acl_check( objects.obid,  ) AS mode FROM objects
WHERE mode > 0;
This gives me a: ERROR:  column "mode" does not exist
You can't access column output alias in where clause. Instead you have 
to use your function twice:

SELECT *, acl_check( objects.obid,  ) AS mode FROM objects
WHERE acl_check( objects.obid,  ) > 0;
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] Stored procedures and "pseudo" fields..

[Markus Bertheau]

Ð ÐÑÑ, 20.07.2004, Ð 15:57, Tomasz Myrta ÐÐÑÐÑ:
> On 2004-07-20 15:34, UÅytkownik Lars Erik Thorsplass napisaÅ:
> > My stored procedure "acl_check()" takes two integers as parameters.
> > Param1 is the object id to check acl on, Param 2 is the object id of
> > the user currently using the system. The procedure returns a positive
> > number (1 or 3 ) if you have some kind of access to the object. As one
> > might understand I want the returned value from the acl_check()
> > procedure to be a part of the result set.
> > 
> > Kinda like this:
> > 
> > SELECT *, acl_check( objects.obid,  ) AS mode FROM objects
> > WHERE mode > 0;
> > 
> > This gives me a: ERROR:  column "mode" does not exist
> 
> You can't access column output alias in where clause. Instead you have 
> to use your function twice:
> 
> SELECT *, acl_check( objects.obid,  ) AS mode FROM objects
> WHERE acl_check( objects.obid,  ) > 0;

and if you properly marked the function STABLE and I am not mistaken,
then PostgreSQL is smart enough to execute the function only once per
row.

-- 
Markus Bertheau <[EMAIL PROTECTED]>


---(end of broadcast)---
TIP 3: 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] How do I convice postgres to use an index?

[Vic Ricker]

The plan showed that Postgres did the proper cast on the timestamp.  I 
think part of the problem is that I need to increase the memory 
allocated to effective_cache_size for the optimizer choose the proper 
method.  (Thanks to Richard Huxton for help.)  I've read that if it 
doesn't have enough ram, it will forego the index for a sequential 
scan..  In my case, that's a very poor decision on the optimizer's 
part.  Disabling enable_seqscan seems to generally fix the problem but 
I'm afraid that it might degrade performance elsewhere.  I have 
expermiented with the effective_cache_size and some other settings but 
haven't had as much luck.  I think I need more physical ram.  Will try 
that soon.

-Vic

SZUCS Gabor wrote:
re-checked; it's WITHOUT in both version, but it's irrelevant if you give
the full spec. Well, then maybe it was a difference between 7.2 and 7.3, but
again, it's irrelevant in your case. Have you tried the typecast?
 

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[SQL] SELECT query/subquery problem

[Caleb Simonyi-Gindele]

I'm still trying to isolate issues with my SELECT query. I have a table in
my veterinary software which stores my sales transactions. The pertinent
columns are dat_staff_code (stores the doctor who gets credit for the sale),
sys_tran_number (stores a unique transaction #), cli_credit_adj_trans_no
(stores the sys_tran_number that a credit is put against).

My problem is that while dat_staff_code is populated for sales, it is not
populated when a credit against a sale is issued. So I end up with data as
follows:

|dat_staff |sys_tran |cli_credit   |cli_tran|
|_code |_number  |_adj_tran_no |_amount |
|--|-|-||
|mm|91112| |50.00   |
|  |95402|91112|-50.00  |

What I want to end up with is net sales (sales - credits) GROUP BY
dat_staff_code. Where I'm stuck is I can't link the credits to a doctor for
the life of me. I use a subquery to get amt where cli_credit_adj_tran_no
matches sys_tran_number but I can't associate it with the doctor of the
original transaction.

This is my query:
SELECT 
(SELECT SUM(cli_tran_amount) 
FROM vetpmardet 
WHERE cli_credit_adj_trans_no 
IN 
(SELECT sys_tran_number from vetpmardet 
WHERE cli_tran_trans_date 
BETWEEN '$pro_week_start_date_mmdd' 
AND '$pro_week_end_date_mmdd'))
+ SUM(cli_tran_amount) AS amount 
FROM vetpmardet 
WHERE cli_tran_trans_date 
BETWEEN '$pro_week_start_date_mmdd' 
AND '$pro_week_end_date_mmdd' 
AND TRIM(dat_staff_code) LIKE 'mm'

Should I be switching to a union query or something?

TIA,
Caleb



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] fail to compare between bytea output in plpgsql

[Peter Wang]

I have a function and am using cursor. All the columns I select in the cursor clause 
are BYTEA datatype.

I need to compare the after-fetch-value for two BYTEA columns which is temp2 and temp3 
shown as below. 
I don't think I can compare it because there is no record in temp table which I use 
for debug purpose.

Does anyone  know how to compare the after-fetched-value for two BYTEA datatype in 
pgsql's function? 



CREATE OR REPLACE FUNCTION pa_revision(INT4, INT4)
RETURNS INTEGER AS '
DECLARE
i_ages ALIAS FOR $1;
i_devs ALIAS FOR $2;

cur1 CURSOR FOR
SELECT a.device_id, a.baseline_revision_id, b.revision_id, b.operational_device_id  
<<- All BYTEA datatype
FROM cm_device a, cm_revision b
WHERE a.device_id = b.operational_device_id
AND current_date - b.creation_date > i_ages;

temp1 cm_device.device_id%TYPE;
temp2 cm_device.baseline_revision_id%TYPE;
temp3 cm_revision.revision_id%TYPE;
temp4 cm_revision.operational_device_id%TYPE;
temp5 INT4;
BEGIN
OPEN cur1;
LOOP
FETCH cur1 INTO temp1, temp2, temp3, temp4;
IF NOT FOUND
THEN
RETURN 1;
END IF;

IF temp2 <> temp3 THEN   << How can I compare the 
value of BYTEA datatype ?
SELECT count(*)
INTO temp5
FROM cm_revision
WHERE operational_device_id = temp4;

 insert into temp values (temp5);
.
END IF;
END IF;

END LOOP;
CLOSE cur1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] locks and triggers. give me an advice please

[Steve Crawford]

> often, I am turning triggers off and on to perform a mass operation
> on a table, and i am interested how should i care of another user
> operations.
>
> the scene is:
> table t1 with user defined triggers
> and many tables reference t1, (so FK triggers defined on t1)
>
> the operation i want to perform on t1 makes a great load to a
> server and have no use in triggers at all.
> the best way to perform this operation is to delete all records,
> modify, and insert them back without changing any adjuscent table.
> (this way takes a few seconds.)
> so i turn off triggers on t1 completely (updating
> pg_class.reltriggers) operate
> and turn on triggers on t1.
>
> it works fine.
>
> the question is:
>
> what should i do to prevent other users of data modification on the
> t1 and the adjuscent tables while triggers is off ?

If I understand your question correctly you should use a transaction 
and lock the table;

begin transaction;
lock t1 in access exclusive mode;

Turn off triggers and do your updates.
(Note, "truncate t1" is faster than "delete from t1" followed by a 
"vacuum full" and you might consider running "reindex table t1" after 
your mass update or if appropriate drop your indexes, load the data, 
then recreate them.)

Re-establish triggers.

commit; --end of transaction unlocks the table


Cheers,
Steve


---(end of broadcast)---
TIP 3: 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] Stored procedures and "pseudo" fields..

[Lars Erik Thorsplass]

On Tue, 20 Jul 2004 09:45:06 -0600, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > Kinda like this:
> >
> > SELECT *, acl_check( objects.obid,  ) AS mode FROM objects
> > WHERE mode > 0;
> 
> Here's the problem. In order to do the select, the query first needs to
> run the where clause.  I.e.:
> 
> select a as test from table where a > 50;
> 
> works, but
> 
> select a as test from table where test > 50;
> 
> fails.  The reason is that when the where clause fires first, there IS
> no test yet, as it hasn't been materialized.  what you need to do is:
> 
> select custom_function(a,b) from table where custom_function(a,b) > 0;
> 
> 

Thanks for clearing that up. I just hoped there was some magic I could
sprinkle on my query to get away from the extra overhead of running
the procedure twice :)

Best regards..

L.E.Thorsplass

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] date_format in postresql

[azah azah]

Hi,
I want convert from mysql to postresql,
in mysql, query to database using the code as below:

date_format(submittime, "%W %M %e, %Y - %r")

what the format query in postresql? Anyone who can help me?

Thanks 
-Azah-

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] surrogate key or not?

[Iain]

Hi,
(B
(Bfor my 2c worth, performance is the least important of the things you need
(Bto consider regarding use of surrogate keys.
(B
(BI use surrogate keys for all situations except the simplest code/description
(Btables, and this is only when the code has no meaning to the application. If
(Bthere is any possibility that you will want to update or re-use codes
(B(attaching a different meaning to them) then surrogate keys are the way to
(Bgo..
(B
(BThus I see it more as an issue of business logic than performance. There are
(Bof course many other considerations with relational theory and stuff like
(Bthat which you could debate endlessly. I expect that googling on "surrogate
(Bkeys" would yeild interesting results.
(B
(BRegards
(BIain
(B
(B- Original Message - 
(BFrom: "Markus Bertheau" <[EMAIL PROTECTED]>
(BTo: <[EMAIL PROTECTED]>
(BSent: Tuesday, July 20, 2004 9:16 PM
(BSubject: [SQL] surrogate key or not?
(B
(B
(B> Hi,
(B>
(B> I have a database that has types in them with unique names. These types
(B> are referenced from other tables through a surrogate integer key. I'm
(B> now wondering if I should eliminate that surrogate key and just use the
(B> name as the primary key. Afaiu, surrogate keys are primarily there to
(B> make joining tables or otherwise searching for a record faster, because
(B> it's faster to compare two integers than it is to compare two strings.
(B>
(B> Now when I want to search for a type in types or another table that
(B> references types(type_id), under what circumstances is it advisable to
(B> have a surrogate integer key and not use the unique type name? Is
(B> searching for an integer as fast as is searching for a string when both
(B> have an index? How many records in the type table do I need to make a
(B> surrogate key a not unsignificantly faster way to retrieve a row? What
(B> about joins? Are these the right questions?
(B>
(B> Thanks.
(B>
(B> -- 
(B> Markus Bertheau <[EMAIL PROTECTED]>
(B>
(B>
(B> ---(end of broadcast)---
(B> TIP 6: Have you searched our list archives?
(B>
(B>http://archives.postgresql.org
(B
(B
(B---(end of broadcast)---
(BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] date_format in postresql

[Rod Taylor]

On Tue, 2004-07-20 at 20:55, azah azah wrote:
> Hi,
> I want convert from mysql to postresql,
> in mysql, query to database using the code as below:
> 
> date_format(submittime, "%W %M %e, %Y - %r")

to_char(submittime, 'format string')

http://www.postgresql.org/docs/7.4/static/functions-formatting.html



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[SQL] Inherited tables and new fields

[Jeff Boes]

This feels like a flaw in the way inherited tables work.
I have a "template" table used to create other tables (but not by
inheritance; instead the "daughter" tables are created via
  create table draft_00123 as select * from draft_template where false;
This is done for somewhat historical reasons, because we weren't sure at
the time if we were going to stay with Pg and so we didn't use every
Pg-specific feature in the books.
Of course, we regret that ...
Now we have a function that spans all the daughter tables. That is, you
can do
  select * from fn_all_drafts() ...
and get rows from each table.
Of course, had we used table inheritance, we'd do something like ...
  select * from draft_template ...
but it wouldn't do exactly what we are doing now: that is,
fn_all_drafts() returns not only the contents of every row in the tables
draft_X, but also an extra column indicating which table that row
came from.
  create table all_drafts (editor_id integer) inherits draft_template;
What frustrates me from time to time is that if "draft_template" is
altered to add a new column, then the function breaks because the new
column appears in "all_drafts" as *following* editor_id. The column
order messes up the code in the function, because it's expecting
all_drafts to look like draft_template, with editor_id added at the end.
Is this a mis-feature?
--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)

Jeffery Boes <>< [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: 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] FOR-IN-EXECUTE, why fail?

[Gaetano Mendola]

Marcos Medina wrote:
I wrote the following:
CREATE OR REPLACE FUNCTION escritorio.seq_valor_actual( text) 
 RETURNS integer AS '
 DECLARE
 secuencia ALIAS FOR $1;
	 valor_actual integer := 0;
	 v_query text;
	 actual integer;
 BEGIN
 RAISE NOTICE ''el parametro tiene el siguiente valor %'' ,secuencia;
 
 v_query := ''SELECT last_value FROM '' || quote_ident(secuencia);
 RAISE NOTICE ''la sentencia a ejecutar es %'' ,v_query;
 
 FOR actual IN EXECUTE v_query LOOP
 	valor_actual := actual;
 END LOOP;
  
 RETURN valor_actual;
 
 END;
 'LANGUAGE 'plpgsql';

And i call:
select seq_valor_actual('s_id_reserva');
The s_id_reserva is a sequence. The error is the following:
WARNING:  plpgsql: ERROR during compile of seq_valor_actual near line 12
ERROR:  missing .. at end of SQL expression
I think the problem is in the FOR but i see correct all.
Can i help me?
Any idea?

You shall declare actual as RECORD and perform inside the loop:
valor_actual = actual.last_value;

Regards
Gaetano Mendola


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] date_format in postresql

[azah azah]

Thanks Chris and Rod.

I think I have a problem because not many function existing in
postresql that i installed.
How can i get all the build-in functions because the basic function
to_char is not existing in the database?

On Tue, 20 Jul 2004 22:39:38 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote:
> On Tue, 2004-07-20 at 20:55, azah azah wrote:
> > Hi,
> > I want convert from mysql to postresql,
> > in mysql, query to database using the code as below:
> >
> > date_format(submittime, "%W %M %e, %Y - %r")
> 
> to_char(submittime, 'format string')
> 
> http://www.postgresql.org/docs/7.4/static/functions-formatting.html
> 
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match
>

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org