Re: [SQL] polymorphic functions and domains

[TJ O'Donnell]

I'm not sure I can propose a scheme that would
work in all situations, but one could simply
considered the built-in data type underlying
the domain.  In my case, this would mean deciding
between character varying and bytea, which seems to
work just fine.

As far as surprising behavior, isn't the scheme
for casting literals already prone to surprises?

TJ

Tom Lane wrote:

"TJ O'Donnell" <[EMAIL PROTECTED]> writes:

I really want two polymorphic functions, one taking
a domain data type using varchar and one bytea.


These aren't polymorphic functions, actually; they're just overloaded.


In that case, postgres complains that it cannot decide
which one to use when called with the
untyped literal 'abc'.


Yeah, functions taking domains as arguments are problematic.  I believe
that with the current resolution rules, a function taking a domain can
only "win" an ambiguous-function comparison if it's an exact match
to the input types --- which in this case means you have to cast the
literal to the domain type.

There's been some talk of trying to rejigger the resolution rules to
make them more friendly to functions that're declared to take domains,
but no one's put forward any concrete proposal.  It's not at all clear
to me how to do it without introducing a lot of surprising behavior :-(

regards, tom lane


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] INSERT INTO relational tables

[Steve Midgley]

At 09:23 AM 12/7/2007, [EMAIL PROTECTED] wrote:

Date: Fri, 07 Dec 2007 14:22:26 +0100
From: "Stefan Scheidegger" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: INSERT INTO relational tables
Message-ID: <[EMAIL PROTECTED]>

Hi all

I'm new to SQL and I'm facing a problem I can't 
find any information about (google, forums). By 
now I wonder if I understood something wrong about relational DBs.


An example to explain my Problem:
Lets say I have a table containing information 
about the customer (name, address, ...) and 
about his order (pieces, product-name, price). 
Because one customer can order several products 
I split the table into two relational tables to prevent redundancy:


tbl_customer (cust_id, cust_name, cust_address)
and
tbl_order (ord_pieces, ord_productname, 
ord_price, ord_customer REFERENCES tbl_customer(cust_id))


Now I want to insert several new customers with 
several orders each at once. If I had all 
information in one table, this would be easy with something like:


INSERT INTO tbl_customerorders (name, address, 
pieces, porductname, price) VALUES ('MR. X', 
'1st street', 3, 't-shirts', 30), ('MR. X', '1st 
street', 5, 'books', 50),  ('MRS. Y', '2nd 
street', 1, 't-shirt', 10),...


But how can I do this in one query if I split 
the table? I can add one new customer, get his 
ID with curval() and then add his orders. But 
this won’t work if I want to add several customers at once.


To read this information I can do a query with 
the argument WHERE cust_id=ord_customer. I can 
create a VIEW doing this so I can read the data 
as if it was stored in only one table. But is 
there in posgres/sql an abstraction-layer that 
allows me to insert as if the information was 
stored in one table? (Something like a VIEW that 
provides INSERT, UPDATE, … and automatically 
inserts the referenced ID.)


Thanks for any help!
Stefan


Andrea has given you some "deep" answers (i.e. 
smarter than what I'm going to say) but since you 
say you're a newbie to SQL maybe this will help.


Perhaps all you need is to wrap your entire set 
of statements into a transaction 
(http://www.postgresql.org/docs/8.2/interactive/tutorial-transactions.html) 
to ensure that your entires are "acid" 
(http://databases.about.com/od/specificproducts/a/acid.htm).


BEGIN
insert tbl_customer...;
insert tbl_order...;
insert tbl_order...;
END

It's not clear whether you're looking for syntax 
simplicity or referential integrity (or both). If 
syntax is your need, then Andreas has given some good ideas.


As another thought about syntax enhancement: 
there are lots of object-relational mappers out 
there that let you model your relations in a 
higher level language (I use Ruby and 
ActiveRecord which are easy to write and learn, 
but Java, .NET, etc all have great stuff). Then 
you just build your "entities" in the domain 
specific language and it handles all the id 
inserts and relational mapping for you. They can 
even handle mapping many-to-many joined entities, 
if you're careful in setting it up.


I hope this is helpful,

Steve






---(end of broadcast)---
TIP 6: explain analyze is your friend