从PostgreSQL函数获取数据到java [英] Get data from PostgreSQL function to java

问题描述

我在 PostgreSQL 数据库中写了一个简单的函数。从我的 JAVA 源代码中，我调用了这个函数，如

  SELECT getData （'活性'）; 
  

我得到的数据正确，但数据集的表头显示我的函数名称（ getdata ）不是 userid 和用户名。在这种情况下，我可以如何获得数据？

  CREATE或REPLACE FUNCTION getData（值文本）
 RETURNS TABLE（
 userid integer，
 username varchar（50）
）AS  - 文本AS  -  
 $ body $ 
 DECLARE 
 fullsql TEXT; 
记录RECORD; 
 exeQuery TEXT; 
 BEGIN 
 
 fullsql：='选择用户名，user_index的用户名where status ='''value''''; 
 
 exeQuery：='SELECT * FROM（'|| fullsql ||'）AS records'; 
 
 RETURN QUERY EXECUTE exeQuery; 
 
 END 
 $ body $ 
语言plpgsql; 
  

目前输出是

  getdate 
 -------------- 
 501，alexanda 
 502，cathie 
  

但是我想输出如下：

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

我试图达到以下目的：

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

$ b $ p

以下查询工作正常：

$ p $ SELECT $ usr.username FROM cust_invoice_index （usr.userid = inv.userid_edit）
INNER JOIN
（SELECT用户名，来自user_index的用户名where status ='active'）作为inv
INNER JOIN
/ pre

解决方案

当你的函数返回一个结果集时，你应该使用 select * from getdata（'活跃'）。

不要将返回函数设置到选择列表中。

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

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)

解决方案

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

这篇关于从PostgreSQL函数获取数据到java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！