[SQL] Insert Function

[David Durst]

Is there anyway to create a insert function?
I am trying:
CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4)
RETURNS int4 AS 'INSERT INTO usr
(user_name,first_name,last_name,permission_set_id,customer_id) values
($1,$2,$3,$4,$5)' language 'sql';

and get:

ERROR:  function declared to return integer, but final statement is not a
SELECT
I thought that a insert would return a internal row #, but I am not sure
about this.

Thanks,
David Durst
MIS Manager
www.la-rubber.com



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Returning rows from functions

[David Durst]

I was wondering if there was a way of returning a complete row from a
function, in reading the documentation of CREATE FUNCTION. I was under the
impression that you could return a row by using setof, but this does not
seem to be true.
Can anyone help?



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

[SQL] Returning row or rows from function?

[David Durst]

I want to create a function that will return
a row or rows of a table is this possible?

If so can someone replay with a complete example?



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

[SQL] returning setof in plpgsql

[David Durst]

I have a function that I want to return setof a table in plpgsql.

Here is what I have:

CREATE FUNCTION lookup_account(varchar(32)) RETURNS SETOF accounts AS '
DECLARE
  aname ALIAS FOR $1;
  rec RECORD;
BEGIN
  select into rec * from accounts where accountname = aname;
  return rec;
END;'
LANGUAGE 'plpgsql';

This seems to hang when I attempt to select it using:

select accountid(
lookup_account('some account')),
accountname(lookup_account('some account')),
type(lookup_account('some account')),
balance(lookup_account('some account'));

Does anyone see a problem w/ my approach??




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

[SQL] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index

[David Durst]

Can anyone tell me why postgres is creating a implicit index when
I already have a PKEY specified

Or am I just interpreting this all wrong?

Here is the entry I am putting in:

create sequence journal_line_id_seq increment 1 start 1;

create table journal_lines (
  journal_line_id int4 PRIMARY KEY DEFAULT NEXTVAL('journal_line_id_seq'),
  entry_id int4,
  account_id int4,
  line_type int2 CHECK (line_type >= 1 AND line_type <= 2),
  line_amount money,
  CONSTRAINT eid FOREIGN KEY(entry_id) REFERENCES journal(entry_id),
  CONSTRAINT aid FOREIGN KEY(account_id) REFERENCES accounts(account_id)
);

Here is the notice postgres spits out:
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'journal_lines_pkey' for table 'journal_lines'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE




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

http://archives.postgresql.org

[SQL] Scheduling Events?

[David Durst]

Is there anyway to schedule DB Events based on time?
So lets say I had a table w/ depreciation schedules in it,
I would like the DB to apply the formula and make the entries on the END
of every month.



---(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] Scheduling Events?

[David Durst]

On a side note, if the DB doesn't support this capability.
Does anyone see a HORRIBLE issue w/ creating a C func
something of this nature.


int handle_temporal_events() {
  if(fork == 0) {
//In here we monitor what time it is
//And maintain a Datastructure w/ events
//And update it every so often
//Then preform various background tasks
  }
  else if(fork == -1) {
//Thread error
  }
}



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

http://archives.postgresql.org

Re: [SQL] Scheduling Events?

[David Durst]

> On Thu, 23 Jan 2003, David Durst wrote:
>
>> Is there anyway to schedule DB Events based on time?
>
> Yes! cron
>
>> So lets say I had a table w/ depreciation schedules in it,
>> I would like the DB to apply the formula and make the entries on the
>> END of every month.
> On Thu, 23 Jan 2003, David Durst wrote:
>
>> Is there anyway to schedule DB Events based on time?
>
> Yes! cron
>
>> So lets say I had a table w/ depreciation schedules in it,
>> I would like the DB to apply the formula and make the entries on the
>> END of every month.

Here is the basic problem w/ using CRON in an accounting situation.

I can't be sure that cron will always be up when the DB is up,
so lets say crond goes down for some random reason (User, System error,
Etc..)

And outside adjustment is made to lets say the equipment account and that
adjustment was made on the value of the equipment, BUT it hadn't been
depreciated because crond went down and no one notice.

Now I have a HUGE issue!

So I have to be sure that all entries/adjustments are made accurately in
the time frame they were meant to happen in.



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Scheduling Events?

[David Durst]

here is a possible NON-Cron solution that a friend of mine came up w/

1) Create a table w/ scheduled events and Account Ids attached to them.
2) Create a table w/ temporal event execution timestamps.
3) On journal entry check to see if there any schedule events for the Account
4) Check timestamp table for last execution
   If Last execution is out of range
 force execution
   Else
 continue as normal

This is passive but it should allow for data integrity w/ out the need of
a external system.






---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Function for adding Money type

[David Durst]

Are there functions for adding and subtracting this type from itself?
Or is there a simple way to do it?



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Function for adding Money type

[David Durst]

> David,
>
>> Are there functions for adding and subtracting this type from itself?
>> Or is there a simple way to do it?
>
> The MONEY type is depreciated, and should have been removed from the
> Postgres  source but was missed as an oversight.   Use NUMERIC instead.
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
Already done, I found it in another doc.
Thanks though



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

[SQL] ERROR: Cannot display a value of type RECORD

[David Durst]

I recieve this error when executing the following function:

select lookup_journal_entries(to_date('20030125','MMDD'),
  to_date('20030125','MMDD'));

Here is the function itself:

create function lookup_journal_entries(date,date) returns setof journal as '
select * from journal where entry_date >= $1 OR entry_date <= $2'
language 'SQL';

Normally I would expect to see a pointer # returned from the above select
but instead I get this error.



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] ERROR: Cannot display a value of type RECORD

[David Durst]

I figured out the issue, DH stupid mistake

select entry_id, entry_description from
lookup_journal_entries(to_date('20030125','MMDD'),
   to_date('20030125','MMDD'));

> I recieve this error when executing the following function:
>
> select lookup_journal_entries(to_date('20030125','MMDD'),
>   to_date('20030125','MMDD'));
>
> Here is the function itself:
>
> create function lookup_journal_entries(date,date) returns setof journal
> as ' select * from journal where entry_date >= $1 OR entry_date <= $2'
> language 'SQL';
>
> Normally I would expect to see a pointer # returned from the above
> select but instead I get this error.
>
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html




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

http://archives.postgresql.org

[SQL] LONG - Question on dealing w/ numerics

[David Durst]

I have a function that is to create a Accounting JOURNAL entry.
The strange thing is the function works for simple entries such as:

Cash - Debit  100
A/R  - Credit 100

But when I try to trick it or break it for testing purposes (IT DOES BREAK
WHEN IT SHOULDN'T) on a entry like this:

Cash - Debit  100
A/R  - Credit 100
Cash - Credit 100
A/R  - Debit  100
(Which should have a net affect of 0 on both accounts)

But here is the resulting balance on accounts,

Cash Debit  Balance 200
A/R  Credit Balance 200

Here is the function and I can't seem to figure out what is LOGICALLY
wrong and would produce these results.

create function
create_journal_entry_line(integer,integer,integer,numeric(20,2)) returns
INTEGER as '
DECLARE
  eid ALIAS FOR $1;
  aid ALIAS FOR $2;
  ltype ALIAS FOR $3;
  amount ALIAS FOR $4;
  new_balance  NUMERIC(20,2);
  account_type RECORD;
  account  RECORD;
  line RECORD;
BEGIN
  select into account * from accounts where account_id = aid;

  IF NOT FOUND THEN
return -1;
  END IF;

  IF account.account_active = ''f'' THEN
return -1;
  END IF;

  insert into journal_lines (entry_id,account_id,line_type,line_amount)
values (eid,aid,ltype,amount);
  select into line * from journal_lines where entry_id = eid AND
account_id = aid AND ltype = ltype;
  IF NOT FOUND THEN
return -1;
  END IF;

  select into account_type * from account_types where account_type_id =
account.account_type;

  IF account_type.positive_account_balance_type = line.line_type THEN
new_balance := account.account_balance + amount;
  ELSE
new_balance := account.account_balance - amount;
  END IF;
  UPDATE accounts SET account_balance = new_balance WHERE account_id =
account.account_id;
  return line.entry_id;
END;' language 'plpgsql';

P.S. Line type represents 1 = Debit, 2 = Credit.  The
positive_account_balance_type tells eithier if the account should have a
DEBIT or CREDIT balance (Represented the same as line type)



---(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] LONG - Question on dealing w/ numerics

[David Durst]

> "David Durst" <[EMAIL PROTECTED]> writes:
>>   insert into journal_lines
>> (entry_id,account_id,line_type,line_amount)
>> values (eid,aid,ltype,amount);
>>   select into line * from journal_lines where entry_id = eid AND
>> account_id = aid AND ltype = ltype;
>
> I bet that last should be line_type = ltype?
Just to let you know, changing ltype to line_type fixed the problem.

But I still think your point about the function selecting more than
one line is valid.

The problem is, the journal_line_id is not created until the insert
occurs and there is no other unique ident than the journal_line_id.



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Question about passing User defined types to functions

[David Durst]

is there a example on how to pass user defined types into
a function??

What I am looking for is something of this nature.

CREATE TYPE dumby_type AS (dumby_id int4, dumby_name text);

create function kick_dumby(dumby dumby_type) returns INTEGER AS '
DECLARE
  somenumber integer;
BEGIN
  return 1;
END;
' language 'plpgsql';


Is there some way of doing this, because the above doesn't work.



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Postgres MD5 Function

[David Durst]

Does there exsist a MD5 Function I can call???

If not, is there any interest in one?



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

http://archives.postgresql.org

Re: [SQL] Postgres MD5 Function

[David Durst]

Is there anywhere I can get these in binary?
Or is my only option to compile Postgres from source??
> Larry Rosenman wrote:
>> --On Friday, January 31, 2003 01:34:42 -0800 David Durst
>> <[EMAIL PROTECTED]> wrote:
>>> Does there exsist a MD5 Function I can call???
>>
>> look at /contrib/pgcrypto in the source distribution.
>>
>
> Also worth noting is that 7.4 will have (and cvs HEAD has) a builtin md5
> function:
>
> regression=# select md5('Joe');
> md5
> --
>   3a368818b7341d48660e8dd6c5a77dbe
> (1 row)
>
> HTH,
>
> Joe
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Commenting PLPGSQL

[David Durst]

Is it possible to have comment lines inside PLPGSQL??



---(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