On Sun, Aug 24, 2014 at 3:26 PM, Tim Bunce <[email protected]> wrote:
> On Sun, Aug 24, 2014 at 08:07:03AM -0700, Chris Travers wrote:
> > Hi everyone;
> > I am nearing completion on the logic to serialize composite types
> into textual representation of tuples
> > for LedgerSMB and I am wondering if there is additional interest from
> others who use DBD::Pg.
>
> > In case it isn't clear what I am talking about is:
> > given a type:
> > CREATE TYPE foo (
> > bar text,
> > baz text
> > );
> > and a hashref {bar => 'foo', baz => 'this, or else that'}
> > it should produce (foo,"this, or else that")
>
> I'm not following you Chris.
> What's the "it" here and where does it fit into the data flow?
>
It would be the serializer.
What I am looking at specifically for my use case is that I can have an
object which is tied to a composite type's structure in the db. This could
then be passed into stored procedures. Here's a basic example:
CREATE TYPE journal_entry_iface (
id int,
source text,
description text,
post_date date,
approved bool,
lines journal_line[]
);
where journal_line is another composite type (here probably a table)
Then I can have a function like:
CREATE OR REPLACE FUNCTION save(self journal_entry_iface)
RETURNS BOOL
LANGUAGE SQL AS
$$
INSERT INTO journal_entry(source, description, post_date, approved)
VALUES (self.source, self.description, self.post_date, self.approved);
INSERT INTO journal_line
SELECT * FROM self.lines;
SELECT TRUE;
$$;
>
> Could you post some example code, ideally showing values of type "foo"
> in use in a table and showing both SELECT and bind/INSERT use-cases?
>
However, the above could be done for straight inserts too, since you can
use composite types in columns in PostgreSQL.
So you could have:
CREATE TYPE currency_amount (
currency char(3),
amount numeric
);
CREATE FUNCTION text(currency_amount) LANGUAGE SQL AS
$$ SELECT $1.amount::text || ' ' || $1.currency; $$;
You could then serialize or deserialize currency amount from your
application but call text() around it to display it nicely in reports.
This might be handy for multicurrency applications (though there are of
course other ways to do that).
>
> Tim.
>
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more
