Put variable values on time interval (from : Re: [GENERAL] Get interval in months)
>From: "A. Kretschmer" <[EMAIL PROTECTED]>
>
>select into v_output ((v_timeout - v_timein) ...
>
>Done, works for me.
>
ok, maybe you mean :
select ((v_timeout - v_timein) - interval ''v_timebreak minutes'') into
v_output;
and then if i try to execute my function on pgAdmin with command :
select scmaster.pr_gettimeinterval('0830'::time,'1700'::time,60);
i got error message :
NOTICE: -- BOF --
ERROR: invalid input syntax for type interval: "v_timebreak minutes"
CONTEXT: SQL statement "SELECT (( $1 - $2 ) - interval 'v_timebreak
minutes')"
PL/pgSQL function "pr_gettimeinterval" line 7 at select into variables
** Error **
ERROR: invalid input syntax for type interval: "v_timebreak minutes"
SQL state: 22007
Context: SQL statement "SELECT (( $1 - $2 ) - interval 'v_timebreak
minutes')"
PL/pgSQL function "pr_gettimeinterval" line 7 at select into variables
if i defined the query with :
SELECT (( $1 - $2 ) - interval '60 minutes') into v_output
it's work but how can i changed my numeric values '60 minutes' into variable on
function, so i can put other value.
Thank you again
Alam Surya
--
OLD MESSAGE :
--
Dear Expert,
I have a function to getting time interval bellow :
create or replace function scmaster.pr_gettimeinterval(time without time zone,
time without time zone, numeric(5,2)) returns char(10) As '
declare v_timeinalias for $1;
v_timeout alias for $2;
v_timebreak alias for $3;
v_outputchar(10);
begin
raise notice ''-- BOF --'';
v_output := select ((v_timeout - v_timein) - interval ''v_timebreak
minutes'');
raise notice ''-- EOF --'';
return v_output;
end;'
language plpgsql;
and when i compilled from pgAdmin, i got some error message bellow :
ERROR: syntax error at or near "select"
LINE 1: SELECT select (( $1 - $2 ) - interval 'v_timebreak minute...
^
QUERY: SELECT select (( $1 - $2 ) - interval 'v_timebreak minutes')
CONTEXT: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7
** Error **
ERROR: syntax error at or near "select"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7
How can i to put my variable "v_timebreak" into function ? so i can send
dynamic value for v_timebreak.
please help, thank you.
Alam Surya
Re: Put variable values on time interval (from : Re: [GENERAL] Get interval in months)
am Mon, dem 10.11.2008, um 13:13:05 +0700 mailte dbalinglung folgendes: > >From: "A. Kretschmer" <[EMAIL PROTECTED]> > > > >First, please create a new thread for a new question. > > > > > > Sorry, ok i create new thread his is still the old thread, see the References-Headers. > >select into v_output ((v_timeout - v_timein) ... > > > > > >(not tested) > > > > please tested your answer so you can find out the result from your own > sugestion, but thank you for your attention. Done, works for me. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Put variable values on time interval (from : Re: [GENERAL] Get interval in months)
From: "A. Kretschmer" <[EMAIL PROTECTED]> First, please create a new thread for a new question. Sorry, ok i create new thread Rewrite the line v_output := select ((v_timeout - v_timein) ... to: select into v_output ((v_timeout - v_timein) ... (not tested) please tested your answer so you can find out the result from your own sugestion, but thank you for your attention. Thanks, Alam Surya -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get interval in months
am Mon, dem 10.11.2008, um 12:06:04 +0700 mailte dbalinglung folgendes: > Dear Expert, First, please create a new thread for a new question. > > I have a function to getting time interval bellow : > > create or replace function scmaster.pr_gettimeinterval(time without time zone, > time without time zone, numeric(5,2)) returns char(10) As ' > declare v_timeinalias for $1; > v_timeout alias for $2; > v_timebreak alias for $3; > v_outputchar(10); > begin > raise notice ''-- BOF --''; > v_output := select ((v_timeout - v_timein) - interval ''v_timebreak > minutes''); > > raise notice ''-- EOF --''; > return v_output; > end;' > language plpgsql; > > > and when i compilled from pgAdmin, i got some error message bellow : > > ERROR: syntax error at or near "select" > LINE 1: SELECT select (( $1 - $2 ) - interval 'v_timebreak minute... > ^ > QUERY: SELECT select (( $1 - $2 ) - interval 'v_timebreak minutes') > CONTEXT: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7 > > ** Error ** Rewrite the line v_output := select ((v_timeout - v_timein) ... to: select into v_output ((v_timeout - v_timein) ... (not tested) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get interval in months
Dear Expert,
I have a function to getting time interval bellow :
create or replace function scmaster.pr_gettimeinterval(time without time zone,
time without time zone, numeric(5,2)) returns char(10) As '
declare v_timeinalias for $1;
v_timeout alias for $2;
v_timebreak alias for $3;
v_outputchar(10);
begin
raise notice ''-- BOF --'';
v_output := select ((v_timeout - v_timein) - interval ''v_timebreak
minutes'');
raise notice ''-- EOF --'';
return v_output;
end;'
language plpgsql;
and when i compilled from pgAdmin, i got some error message bellow :
ERROR: syntax error at or near "select"
LINE 1: SELECT select (( $1 - $2 ) - interval 'v_timebreak minute...
^
QUERY: SELECT select (( $1 - $2 ) - interval 'v_timebreak minutes')
CONTEXT: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7
** Error **
ERROR: syntax error at or near "select"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7
How can i to put my variable "v_timebreak" into function ? so i can send
dynamic value for v_timebreak.
please help, thank you.
Alam Surya
- Original Message -
From: "Andreas Kretschmer" <[EMAIL PROTECTED]>
To:
Cc: "Gerhard Heift" <[EMAIL PROTECTED]>
Sent: Saturday, November 08, 2008 19:59
Subject: Re: [GENERAL] Get interval in months
> Gerhard Heift <[EMAIL PROTECTED]> schrieb:
>
>> Hello,
>>
>> I want to get an interval in months from two dates:
>>
>> SELECT '2008-02-01'::timestamp - '2008-01-01'::timestamp AS '1 month'
>>
>> Here I want '1 month' and not '31 days' as answer.
>
> How long is a month? 28 days? 31 days? But okay, i will try:
>
> test=*# SELECT justify_days('2008-02-01'::timestamp -
> '2008-01-01'::timestamp) ;
> justify_days
> --
> 1 mon 1 day
> (1 row)
>
> Is this okay for you? PostgreSQL assume 30 days per month.
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] Chart of Accounts
James, It is not good practice to delete an account with out first transfering the amount in that account to another account. You will also need to make sure the account has a zero balance before deleting it. You will also need to log the transactions if funds are moved between accounts with a reason why they were transfred. To me a "intelegent" accounting system means that when you make an entry in one account, the system automatically makes a corresponding entry on the other side of the equal sign. Example credit Office Supplies the system debits Cash On Hand (or what ever account is used to pay for office supplies). The issue on the update, try using an if statement like If new.amt != old.amt Then Do Amount Changes that you already have in place End if The database should then go ahead an update the parent wtihout an issues. If that does not work create a function that drops the trigger, update the table and then creates the trigger. I am sure that this type of change (moving accounts) will not be a common thing once the COA has been set up and in use for a while. HTH. Michael > Date: Mon, 10 Nov 2008 05:24:03 +0100> From: [EMAIL PROTECTED]> To: [EMAIL > PROTECTED]> Subject: Re: [GENERAL] Chart of Accounts> CC: > pgsql-general@postgresql.org> > Hi James,> > There is some my publications > about SART AML System based on banking> General Ledger (OLAP Data Warehouse > and Chart of Accounts as dimension> with 60 000+ items) - may be helpful.> > > http://www.analyticsql.org/documentation.html> > http://www.analyticsql.org/files/AITM-MoneyLaundering.pdf> > Regards,> Blazej > Oleszkiewicz> > 2008/10/12 James Hitz <[EMAIL PROTECTED]>:> > Dear All,> >> > > I have just started experimenting with PGSQL, with a view to migrate from the > SQL server I use currently. I am trying to implement an "intelligent" Chart > of Accounts for an accounting program. The following is long-winded but > please bear with me:> >> > I have a table coa (chart of accounts) with the > following schema> >> > CREATE TABLE coa(> > coa_id serial not null,> > > parent_id int not null default 0,> > account_name text not null,> > amt money > default 0,> > primary key(coa_id)> > );> >> > After populating the database > with basic accounts it resembles this (the hierarchy is mine):> >> > coa_id, > parent_id, account_name, amt> > 0, -1, 'Chart of Accounts', 0.00> > 1, 0, > 'Assets', 0.00> > 5, 1, 'Fixed Assets', 0.00> > 6, 5, 'Motor Van', 0.00> > > --truncated ---> > 2, 0, 'Liabilities', 0.00> > 3, 0, 'Income', 0.00> > 4, 0, > 'Expenses', 0.00> >> > So far, so good. I would like it so that if the amt of > a a child account changes, the parent account is updated, if a child account > is deleted, the amount is reduced off of the parent account etc.> >> > I have > managed to achieve this using the following trigger functions:> >> > CREATE > OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS> > $body$> > > begin> > update coa set amt = amt - old.amt where coa_id = old.parent_id;> > > return old;> > end;> > $body$> > LANGUAGE 'plpgsql'> >> > --> > >> > CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS> > > $body$> > begin> > UPDATE coa SET amt = amt + new.amt WHERE coa_id = > new.parent_id;> > return new;> > end;> > $body$> > LANGUAGE 'plpgsql'> >> > > > >> > CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS > trigger AS> > $body$> > begin> > IF new.parent_id = old.parent_id THEN> > > UPDATE coa SET amt = amt + (new.amt - old.amt)> > WHERE coa_id = > new.parent_id;> > ELSE> > UPDATE coa SET amt = amt - old.amt> > WHERE > parent_id = old.parent_id;> > UPDATE coa SET amt = amt + new.amt> > WHERE > parent_id = new.parent_id;> > END IF;> > RETURN new;> > end;> > $body$> > > LANGUAGE 'plpgsql'> >> > > >> > These have been bound to the > respective ROW before triggers. And they work as expected upto a certain > extent. eg assigning a value to 'Motor Van' updates the relevant parent > accounts:> >> > UPDATE coa SET amt = 4000 WHERE coa_id = 6;> >> > The problem > comes about when one wants to change the parent account for a sub account eg, > assuming in the example above that 'Motor Van' was a liability, attempting to > change its parent_id from 1 to 2 is erronous and somewhat interesting because > the amt for all related accounts are reset to unpredictible values, AND the > parent_id does not change anyway.> >> > The problem lies squarely in the > function coa_upd_amt().> >> > Any ideas.> >> > Thank you.> >> >> >> >> > --> > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)> > To > make changes to your subscription:> > > http://www.postgresql.org/mailpref/pgsql-general> >> > -- > Sent via > pgsql-general mailing list (pgsql-general@postgresql.org)> To make changes to > your subscription:> http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
There is some my publications about SART AML System, where is more detailed described all this things (and more) that I wrote below. http://www.analyticsql.org/documentation.html Regards, Blazej Oleszkiewicz 2008/9/4 Blazej <[EMAIL PROTECTED]>: > Hi Artacus > > 2008/9/4 Artacus <[EMAIL PROTECTED]>: >> Oh, as I was writing a CUBE query today and realized that I forgot to >> mention this. And unlike most gripes, like MERGE INTO or CTE's which are >> really convenience things, this is key piece of functionality that you just >> can't reproduce in Postgres. >> > > It is not true. First look at this: > http://www.analyticsql.org/ > > In atachment is two snapshots from production system (Analytic SQL > Server based on PostgreSQL 8.3): > > Picture: non_uniform_dimension.JPG > > Heterogeneous data warehouse dimensions where each class of dimension: > Chart of Account (PK), Synthetic Account (KS), Analytical Account (KA) > may have a heterogeneous structure of the hierarchy (~60 000 items in > hierarchy, max depth 9). > > For example, on picture marked heterogeneity of the hierarchy: > -- Green: [PK, KS "operations involving cash and interbank > operations," KS "Kasa," KS "Cash / banknotes and coins /" KA "Cash in > hand GBP"] > -- Red: [PK, KS "operations involving cash and interbank operations," > KS "Kasa," KS "bankers' cheques," KS "foreign bankers' cheques," KS > "bankers' cheques, foreign currency," KA "bankers' cheques in USD "] > > Hence, we can see that both hierarchical structure: > -- [PK, KS, KS, KS, KA] for a KA "Cash in hand GBP (ID: 339 in column KNT_ID); > -- [PK, KS, KS, KS, KS, KS, KA] for a KA "foreign bankers' cheques in > U.S. dollars" (ID: 363 in column KNT_ID) > > are different from each other in terms of the structure of the hierarchy. > > Picture:olap_raport_non_uniform_dimension.JPG - example of OLAP raport > based on Chart of Account. > > so it is much more then "OLAP" ORACLE extensions like CUBE etc. > > Regards, > Blazej Oleszkiewicz > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Chart of Accounts
Hi James, There is some my publications about SART AML System based on banking General Ledger (OLAP Data Warehouse and Chart of Accounts as dimension with 60 000+ items) - may be helpful. http://www.analyticsql.org/documentation.html http://www.analyticsql.org/files/AITM-MoneyLaundering.pdf Regards, Blazej Oleszkiewicz 2008/10/12 James Hitz <[EMAIL PROTECTED]>: > Dear All, > > I have just started experimenting with PGSQL, with a view to migrate from the > SQL server I use currently. I am trying to implement an "intelligent" Chart > of Accounts for an accounting program. The following is long-winded but > please bear with me: > > I have a table coa (chart of accounts) with the following schema > > CREATE TABLE coa( >coa_id serial not null, >parent_id int not null default 0, >account_name text not null, >amt money default 0, >primary key(coa_id) > ); > > After populating the database with basic accounts it resembles this (the > hierarchy is mine): > > coa_id, parent_id, account_name, amt > 0,-1, 'Chart of Accounts',0.00 > 1, 0, 'Assets',0.00 > 5, 1, 'Fixed Assets',0.00 > 6, 5, 'Motor Van', 0.00 > --truncated --- > 2, 0, 'Liabilities', 0.00 > 3, 0, 'Income', 0.00 > 4, 0, 'Expenses',0.00 > > So far, so good. I would like it so that if the amt of a a child account > changes, the parent account is updated, if a child account is deleted, the > amount is reduced off of the parent account etc. > > I have managed to achieve this using the following trigger functions: > > CREATE OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS > $body$ > begin >update coa set amt = amt - old.amt where coa_id = old.parent_id; >return old; > end; > $body$ > LANGUAGE 'plpgsql' > > -- > > CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS > $body$ > begin >UPDATE coa SET amt = amt + new.amt WHERE coa_id = new.parent_id; >return new; > end; > $body$ > LANGUAGE 'plpgsql' > > > > CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS trigger AS > $body$ > begin >IF new.parent_id = old.parent_id THEN >UPDATE coa SET amt = amt + (new.amt - old.amt) >WHERE coa_id = new.parent_id; >ELSE >UPDATE coa SET amt = amt - old.amt > WHERE parent_id = old.parent_id; >UPDATE coa SET amt = amt + new.amt > WHERE parent_id = new.parent_id; >END IF; >RETURN new; > end; > $body$ > LANGUAGE 'plpgsql' > > > > These have been bound to the respective ROW before triggers. And they work > as expected upto a certain extent. eg assigning a value to 'Motor Van' > updates the relevant parent accounts: > > UPDATE coa SET amt = 4000 WHERE coa_id = 6; > > The problem comes about when one wants to change the parent account for a sub > account eg, assuming in the example above that 'Motor Van' was a liability, > attempting to change its parent_id from 1 to 2 is erronous and somewhat > interesting because the amt for all related accounts are reset to > unpredictible values, AND the parent_id does not change anyway. > > The problem lies squarely in the function coa_upd_amt(). > > Any ideas. > > Thank you. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use index in WHERE int = float
"Andrus" <[EMAIL PROTECTED]> writes: >> The IN-pullup code runs before constant-simplification does, so it >> doesn't see that as a simple join condition. > Seems serious design flaw. > How to change expression parser so that contant parts of expressions are > removed before IN-pullup? It would be far simpler to fix your query generator to not emit the useless "0 or". Even if we made the planner deal with that, the number of cycles it would expend to recover from the generator's stupidity would be several orders of magnitude higher than the number of cycles needed to not be so stupid. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use index in WHERE int = float
The IN-pullup code runs before constant-simplification does, so it doesn't see that as a simple join condition. Seems serious design flaw. How to change expression parser so that contant parts of expressions are removed before IN-pullup? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] again...
On Sat, Oct 25, 2008 at 6:12 PM, Ati Rosselet <[EMAIL PROTECTED]> wrote: > I'm still getting a lot of these entries in my eventlog whenever I have a > reasonably large amount of logging: > > Event Type:Error > Event Source:PostgreSQL > Event Category:None > Event ID:0 > Date:10/22/2008 > Time:9:36:28 AM > User:N/A > Computer:-- > Description: > could not write to log file: Bad file descriptor > > production server 8.3.3 (tried with 8.3.4 - same thing), win 2003 server. > I'd send this to psql-bugs, but I can't figure out how to reproduce it.. and > apparently noone else has seen this? Or is noone else running postgres on > win2003??? (survey says??) The same problem is registered for PG 8.3.3 instance under win2k3 (w/o SP, w/o antivirus) control. Trying to figure out what is the reason and how to solve it (installation is not mine)... This message is just to say that you're not alone :-) Maybe this is common problem for win2k3, will see. -- Sincerely yours, Nikolay Samokhvalov Postgresmen LLC, http://postgresmen.ru -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database access over the Internet...
Michelle Konzack wrote: Am 2008-11-08 19:07:35, schrieb Scott Marlowe: No, your histrionics aside, it's the way this list works by default, and for good reason. If you need it to work differently, there's a setting which has been pointed out to you at two times now. Please take responsibility for your own life and fix the configuration and stop whinging. It does not work since the CCs are coming FROM the sender and NOT from the mailinglist. I'm thinking that if you check the setting per the faq that has been pointed out, that majordomo will note that you've been cc'd directly, and so will not send you the copy from the mailing list. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database access over the Internet...
On 09/11/2008 04:32, Uwe C. Schroeder wrote: > Actually it's polite and pretty much standard to hit "reply all" and I for > one > appreciate it when I'm kept on CC, just because originally I was interested > in the topic discussed and I might miss the response otherwise. There's also the fact that people quite often post to the list without actually being subscribed - so if there were no CC they'd never get an answer to their question. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
