PostgreSQL函数给出所有行，但不是键值对格式

Question

我在PostgreSQL中有一个名为users的表。我想从我的Laravel应用程序获得表中的所有用户。

我可以直接从表中获取:

$testData = DB::table('users')->get();

输出是:

 [{"id":1,"name":"Chris Sevilleja","username":"sevilayha","email":"[e-mail]","password":"$2y$08$i/ATa68ierqRL47ZxHX4EesJGEcdtKPckZs8GDGpYS.IR4aaQn./q","created_at":"2016-09-07 09:32:41","updated_at":"2016-09-07 09:32:41"},{"id":2,"name":"Bemagoni chandrashekar","username":"chandrashekar","email":"[e-mail]","password":"$2y$08$QG9JsAerYp3UYpXNNyImhuz/6hiWv8XpURpJX1uJ.hAm8l1RG2JrC","created_at":"2016-09-07 09:32:41","updated_at":"2016-09-07 09:32:41"},{"id":3,"name":"dwefr","username":"ewre","email":"[e-mail]","password":"$2y$08$s2XBZvoAvpEqjjhbx8QPw.eSe5TIuJC25XbaFkTskzAKAGi99QWga","created_at":"2016-09-07 09:34:27","updated_at":"2016-09-07 09:34:27"},{"id":4,"name":"r3t54y6u677i","username":"retrytyu","email":"[e-mail]","password":"$2y$08$Xb0Xm4KwdSwcBSHX0F6ETOWU60X.NO5D7/uwVv/xUAXTUS8LSPCLu","created_at":"2016-09-07 09:46:26","updated_at":"2016-09-07 09:46:26"},{"id":5,"name":"r3t45y6u","username":"ertrh","email":"[e-mail]","password":"$2y$08$36DLu49nZ2YOWtU.c625meiyi3/fmsHxiTuxIU9z9UcyrbIpFSKKW","created_at":"2016-09-07 10:02:05","updated_at":"2016-09-07 10:02:05"},{"id":6,"name":"ewrtryuyj","username":"ryt","email":"[e-mail]","password":"$2y$08$GULHtX3GGXPGgm8gA9yDbeawZlQ5QwD2TX7nrCvEU4j7jrSgPWAQO","created_at":"2016-09-07 10:04:17","updated_at":"2016-09-07 10:04:17"},{"id":7,"name":"chandu","username":"chandu","email":"[e-mail]","password":"$2y$08$gpAhcl/Sg.lGvb.zk.I/m.PfcttGI6OPFMsMxQVm15dYOtQDvIWSG","created_at":"2016-09-07 10:06:18","updated_at":"2016-09-07 10:06:18"},{"id":8,"name":"dewfergt","username":"erwrgf","email":"[e-mail]","password":"$2y$08$ikYAXV1prZsEj2MPxXM4S.Tqn160Jv25cFOQLghK8ptFiSBaIFGZO","created_at":"2016-09-07 10:07:20","updated_at":"2016-09-07 10:07:20"},{"id":9,"name":"rteryt","username":"wrwter","email":"[e-mail]","password":"$2y$08$pkOUBl1NlNdBShNWiklya.0zlPzPrEH2edCfvdCiHLnj80GY1sdtm","created_at":"2016-09-08 07:11:46","updated_at":"2016-09-08 07:11:46"},{"id":10,"name":"Raghu","username":"raghu","email":"[e-mail]","password":"$2y$08$m9wke.vvTTZytYw91I4/q.qxKoCobLOW7dbCvs66xFyJyy2R9phni","created_at":"2016-09-10 10:06:40","updated_at":"2016-09-10 10:06:40"},{"id":11,"name":"wewert","username":"ewretr","email":"[e-mail]","password":"$2y$08$IXD0eXYTPPGE1MfALonEFey0lr/KBMZ0.3AIO3sWVgu7IZdWhXwTG","created_at":"2016-09-12 07:48:58","updated_at":"2016-09-12 07:48:58"}]

通过PostgreSQL函数调用:

my PostgreSQL function:

-- Function: public."RegiterUsers2"()
-- DROP FUNCTION public."RegiterUsers2"();
CREATE OR REPLACE FUNCTION public."RegiterUsers2"()
  RETURNS SETOF users AS
'select * from users'
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public."RegiterUsers2"()
  OWNER TO postgres;

java代码:

$allUserData=DB::select('SELECT public."RegiterUsers2"()')

输出:

[{"RegiterUsers2":"(1,"Chris Sevilleja",sevilayha,[e-mail],$2y$08$i/ATa68ierqRL47ZxHX4EesJGEcdtKPckZs8GDGpYS.IR4aaQn./q,"2016-09-07 09:32:41","2016-09-07 09:32:41")"},{"RegiterUsers2":"(2,"Bemagoni chandrashekar",chandrashekar,[e-mail],$2y$08$QG9JsAerYp3UYpXNNyImhuz/6hiWv8XpURpJX1uJ.hAm8l1RG2JrC,"2016-09-07 09:32:41","2016-09-07 09:32:41")"},{"RegiterUsers2":"(3,dwefr,ewre,[e-mail],$2y$08$s2XBZvoAvpEqjjhbx8QPw.eSe5TIuJC25XbaFkTskzAKAGi99QWga,"2016-09-07 09:34:27","2016-09-07 09:34:27")"},{"RegiterUsers2":"(4,r3t54y6u677i,retrytyu,[e-mail],$2y$08$Xb0Xm4KwdSwcBSHX0F6ETOWU60X.NO5D7/uwVv/xUAXTUS8LSPCLu,"2016-09-07 09:46:26","2016-09-07 09:46:26")"},{"RegiterUsers2":"(5,r3t45y6u,ertrh,[e-mail],$2y$08$36DLu49nZ2YOWtU.c625meiyi3/fmsHxiTuxIU9z9UcyrbIpFSKKW,"2016-09-07 10:02:05","2016-09-07 10:02:05")"},{"RegiterUsers2":"(6,ewrtryuyj,ryt,[e-mail],$2y$08$GULHtX3GGXPGgm8gA9yDbeawZlQ5QwD2TX7nrCvEU4j7jrSgPWAQO,"2016-09-07 10:04:17","2016-09-07 10:04:17")"},{"RegiterUsers2":"(7,chandu,chandu,[e-mail],$2y$08$gpAhcl/Sg.lGvb.zk.I/m.PfcttGI6OPFMsMxQVm15dYOtQDvIWSG,"2016-09-07 10:06:18","2016-09-07 10:06:18")"},{"RegiterUsers2":"(8,dewfergt,erwrgf,[e-mail],$2y$08$ikYAXV1prZsEj2MPxXM4S.Tqn160Jv25cFOQLghK8ptFiSBaIFGZO,"2016-09-07 10:07:20","2016-09-07 10:07:20")"},{"RegiterUsers2":"(9,rteryt,wrwter,[e-mail],$2y$08$pkOUBl1NlNdBShNWiklya.0zlPzPrEH2edCfvdCiHLnj80GY1sdtm,"2016-09-08 07:11:46","2016-09-08 07:11:46")"},{"RegiterUsers2":"(10,Raghu,raghu,[e-mail],$2y$08$m9wke.vvTTZytYw91I4/q.qxKoCobLOW7dbCvs66xFyJyy2R9phni,"2016-09-10 10:06:40","2016-09-10 10:06:40")"},{"RegiterUsers2":"(11,wewert,ewretr,[e-mail],$2y$08$IXD0eXYTPPGE1MfALonEFey0lr/KBMZ0.3AIO3sWVgu7IZdWhXwTG,"2016-09-12 07:48:58","2016-09-12 07:48:58")"}]

我需要这样

    {"id":1,
"name":"Chris Sevilleja",
"username":"sevilayha",
"email":"[e-mail]",
"password":"$2y$08$i/ATa68ierqRL47ZxHX4EesJGEcdtKPckZs8GDGpYS.IR4aaQn",
"created_at":"2016-09-07 09:32:41",
"updated_at":"2016-09-07 09:32:41"},

所以什么是错误的postgres功能，我怎么能得到像上面一个

Answer 1

当你调用一个返回很多列的函数并且你想要每一个列时，你必须以以下形式调用:

SELECT col1, col2, ... FROM function(...)

或:

SELECT * FROM function(...)

在你的例子中，你只需要:

$allUserData=DB::select('SELECT * FROM public."RegiterUsers2"()')