From: | Stuart Robinson <stuart(at)zapata(dot)org> |
---|---|
To: | Barry Lind <barry(at)xythos(dot)com> |
Cc: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: JDBC inserts on views using rules |
Date: | 2001-11-09 21:54:57 |
Message-ID: | Pine.LNX.4.30.0111091127500.11712-100000@othello.dreamingamerica.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
I didn't give the actual code because it's a bit involved and the details
probably don't matter too much. I'll just give a simplified example that
illustrates my point.
Basically, I've got a view that combines two tables.
CREATE VIEW ex_view AS
SELECT u.url, i.name
FROM url u, url_info i
WHERE u.id = i.id_url
I also have a rule like the following to intercept inserts to the view:
CREATE RULE ex_view_insert AS
ON INSERT TO ex_view
DO INSTEAD
select ex_function(new.url, new.name)
The function inserts into the tables that make up the view by doing
something like this (the logic is more compicated, hence the necessity
for a function, but this will give you an idea of what I'm doing):
CREATE FUNCTION ex_function(TEXT, TEXT)
RETURNS INTEGER
AS '
DECLARE
BEGIN
INSERT INTO url (url) VALUES ($1);
INSERT INTO url_info (name) VALUES ($2);
RETURN 1;
END;
'
LANGUAGE 'plpgsql';
This works fine when you run it manually in psql, but when it's run by the
JDBC, you get an error, because it returns a value (1) (since I
called the function with a select). However, the application doesn't
expect a return value, since it
thought it was doing an insert and used the executeUpdate method.
So, is there some way of supressing the return value so that the
rule-redirected insert will succeed? Could I call the function w/
something other than select? I hope the problem is clear and that there's
a straightforward solution, but if functions necessarily return values
unless they're called as triggers, I might be out of luck.
Thanks.
-Stuart
On Fri, 9 Nov 2001, Barry Lind wrote:
> Stuart,
>
> What is the sql statement you are issuing that is causing this error.
> Without seeing the sql statement I am having a difficult time
> understanding exactly what you are trying to do.
>
> thanks,
> --Barry
>
>
> Stuart Robinson wrote:
>
> > I sent this to the general mailing list, but I thought it might be
> > appropriate for this forum, since it does relate to the JDBC.
> >
> > Regards,
> > Stuart Robinson
> >
> > ---------- Forwarded message ----------
> > Date: Thu, 8 Nov 2001 23:51:33 -0800 (PST)
> > From: Stuart Robinson <stuart(at)zapata(dot)org>
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: [GENERAL] inserts on views using rules
> >
> > I've got a view that combines a couple of tables. The view is meant to
> > simplify interactions with a Java application, so that selects, inserts,
> > and updates can be made on the view as if it were a real table. So, I
> > figured that rules would be the way to go. For inserts, I wrote a
> > do-instead rule for the view which in turn calls a function. The function
> > is meant to take the values from the insert statement and stick them into
> > the appropriate tables. However, functions appear to always return a value
> > (except as triggers), which confuses the JDBC. So, when I do an insert on
> > the view, the function is called and returns a value, causing the
> > following error:
> >
> > A result was returned by the statement, when none was expected.
> > at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:80)
> > at
> > org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122)
> >
> > I take it this is the expected JDBC behavior and not a bug. If so, what
> > are my options given that functions always return values and triggers
> > operate before or after inserts, but not instead of them? Is there some
> > way of calling functions so that they don't return a value? (If this
> > posting is more appropriate for another mailing list, please let me know.)
> > Thanks in advance.
> >
> > -Stuart Robinson
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> >
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Barry Lind | 2001-11-10 02:21:58 | Re: JDBC inserts on views using rules |
Previous Message | Jayesh K. Parayali | 2001-11-09 19:55:33 | Re: JDBC inserts on views using rules |