Get data from PostgreSQL function to java

Question

I have written a simple function in PostgreSQL database. From my JAVA source code I am calling this function like

SELECT getData('active');

I am getting the data correct but the table header of the dataset is showing my function name (getdata) not userid and username. In this situation how I can get data?

CREATE or REPLACE FUNCTION getData(value text) 
RETURNS  TABLE(
    userid integer,
    username varchar(50)
) AS -- text AS --
$body$
DECLARE
    fullsql TEXT;
    records RECORD;
    exeQuery TEXT;
BEGIN

fullsql:= 'SELECT userid, username from user_index where status='''value'''';

exeQuery := 'SELECT * FROM (' || fullsql || ') AS records';

RETURN QUERY EXECUTE exeQuery;

END
$body$
LANGUAGE plpgsql;

Currently the output is

getdate
--------------
501,alexanda
502,cathie

But I want to output like:

userid  username
------|---------
501,alexanda
502,cathie

i am trying to acheive following:

SELECT usr.username FROM cust_invoice_index as inv
INNER JOIN
(SELECT getdata('active')) as usr ON (usr.userid=inv.userid_edit)

Following query is working fine:

SELECT usr.username FROM cust_invoice_index as inv
INNER JOIN
(SELECT userid, username from user_index where status='active') as usr ON (usr.userid=inv.userid_edit)

What do you mean table headers? What does output look like currently? — SpartanElite
– SpartanElite, Commented Dec 18, 2013 at 15:12
Is this output from postgres command line or is it from javacode? If from javacode, please post your code — SpartanElite
– SpartanElite, Commented Dec 18, 2013 at 15:35
@SpartanElite 1st problem I am facing calling function within another function of database. Once it's done then I will try to achieve it from JavaCode — ray
– ray, Commented Dec 18, 2013 at 15:46

user330315 · Accepted Answer · 2013-12-18 15:48:20Z

1

As your function returns a result set you should be using select * from getdata('active').

Don't put calls to set returning functions into the select list.

SELECT usr.username 
FROM cust_invoice_index as inv
 JOIN (SELECT * FROM getdata('active')) as usr ON usr.userid=inv.userid_edit

answered Dec 18, 2013 at 15:48

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

ray Over a year ago

So silly I am. It's so simple and I was thinking a complex way!! Thanks man. It's working fine.

Collectives™ on Stack Overflow

Get data from PostgreSQL function to java

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related