PostgreSQL9.3新增的json_populate_recordset函数使用问题
,想了想，目前postgres9.2已经是最新的发布版本了，最新版本都没有方案解决，我是不是就无可奈何了呢，可是我还是不服气，于是查阅了官方网站的最新用户手册，惊奇发现PostgreSQL 9.3 Beta 1 Released! （2013-05-13发布的），然后看了它的手册，发现有个函数可以将数组json中反过来转化为数据行，一下高兴了，可选没有安装包，只有源码！不过还好我在最近不用windows了（虽然在.NET下开发，但是我越来越讨厌微软了，啥都收钱，连我的最爱的VS都不给用，这由于公司即将上市的原因，微软来“打劫”了，我的vs也改用了开源的SharpDevelop 4.3），改用ubuntu13.4，所以下载了源码从新编译安装，过来一个小时，终于搞定了,下面是我的论证过程：
。
（由于我的台式机ubuntu里面没安装"天朝"输入法，只能将就打“拼音”了
，测试过程的截图，我是放在wiznote笔记里面记录，然后空闲的时候用笔记本整理好发布到博客上来
）1. the following picture show the 9.3 version of postgresql database provide a number of json functions:
now, i try it on what thoes json function can do.firt,we need to create two table for testing,which show with the following:
-- Table: table_users
-- DROP TABLE table_users;
CREATE TABLE table_users
(
xwid uuid NOT NULL,
xwname character varying,
xwage integer,
xwfid uuid,
CONSTRAINT table_user_pk PRIMARY KEY (xwid),
CONSTRAINT table_users_fk FOREIGN KEY (xwfid)
REFERENCES table_adress (xwfid) MATCH Unknown
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE table_users
OWNER TO postgres;
-- Index: fki_table_users_fk
-- DROP INDEX fki_table_users_fk;
CREATE INDEX fki_table_users_fk
ON table_users
USING btree
(xwfid);
================================================================
-- Table: table_adress
-- DROP TABLE table_adress;
CREATE TABLE table_adress
(
xwfid uuid NOT NULL,
xwadress character varying,
CONSTRAINT table_adress_pk PRIMARY KEY (xwfid)
)
WITH (
OIDS=FALSE
);
ALTER TABLE table_adress
OWNER TO postgres;
================================================================
now we need to insert some test data into the two tables;
--insert into the table_adress table
INSERT INTO table_adress(xwfid, xwadress)VALUES ('5b9898f7-4456-4390-be2b-525c3274855a','China');
INSERT INTO table_adress(xwfid, xwadress)VALUES ('6b9898f7-4456-4390-be2b-525c3274855a','Japan');
INSERT INTO table_adress(xwfid, xwadress)VALUES ('7b9898f7-4456-4390-be2b-525c3274855a','America');
INSERT INTO table_adress(xwfid, xwadress)VALUES ('8b9898f7-4456-4390-be2b-525c3274855a','Russia');
--insert into the table_users table
INSERT INTO table_users(xwid, xwname, xwage, xwfid)
VALUES ('1a9898f7-4456-4390-be2b-525c3274855a','成龙',50,'5b9898f7-4456-4390-be2b-525c3274855a');
INSERT INTO table_users(xwid, xwname, xwage, xwfid)
VALUES ('2a9898f7-4456-4390-be2b-525c3274855a','蒼井空',28,'6b9898f7-4456-4390-be2b-525c3274855a');
INSERT INTO table_users(xwid, xwname, xwage, xwfid)
VALUES ('3a9898f7-4456-4390-be2b-525c3274855a','Nicolas Cage',49,'7b9898f7-4456-4390-be2b-525c3274855a');
INSERT INTO table_users(xwid, xwname, xwage, xwfid)
VALUES ('4a9898f7-4456-4390-be2b-525c3274855a','Влади́мир Влади́мирович Пу́тин', 61,'8b9898f7-4456-4390-be2b-525c3274855a');
================================================================
select * from table_adress;
resulte:
select * from table_users;
resulte:
select array_to_json(array_agg(row_to_json(t)))
from (
select u.xwname,u.xwage,a.xwadress from table_users as u,table_adress as a where u.xwfid=a.xwfid
) t
--resulte:'[{"xwname":"成龙","xwage":50,"xwadress":"China"},
-- {"xwname":"蒼井空","xwage":28,"xwadress":"Japan"},
-- {"xwname":"Nicolas Cage","xwage":49,"xwadress":"America"},
-- {"xwname":"Влади́мир Влади́мирович Пу́тин","xwage":61,"xwadress":"Russia"}]'
==============================================================================
select * from json_populate_recordset(null::t_row,'[{"a":1,"b":2},{"a":3,"b":4}]')
ERROR: type "t_row" does not exist
LINE 1: select * from json_populate_recordset(null::t_row,'[{"a":...
********** Error **********
ERROR: type "t_row" does not exist
SQL state: 42704
Character: 48
=============================================================================
CREATE OR REPLACE FUNCTION get_jsonrows(a refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
msgjson character varying :='';
message_row record;
BEGIN
select u.xwname,u.xwage,a.xwadress from table_users as u,table_adress as a where false limit 1 into message_row;
select array_to_json(array_agg(row_to_json(t)))
from (
select u.xwname,u.xwage,a.xwadress from table_users as u,table_adress as a where u.xwfid=a.xwfid
) t into msgjson;
--OPEN a FOR select * from json_populate_recordset(null::message_row,msgjson);
OPEN a FOR select msgjson as msgjson;
RETURN NEXT a;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
====test:resulte
select * from get_jsonrows('a');
fetch all from a;
===data:
'[{"xwname":"成龙","xwage":50,"xwadress":"China"},{"xwname":"蒼井空","xwage":28,"xwadress":"Japan"},{"xwname":"Nicolas Cage","xwage":49,"xwadress":"America"},{"xwname":"Влади́мир Влади́мирович Пу́тин","xwage":61,"xwadress":"Russia"}]'
========================================================================================================
CREATE OR REPLACE FUNCTION get_jsonrows(a refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
msgjson character varying :='';
message_row record;
BEGIN
select u.xwname,u.xwage,a.xwadress from table_users as u,table_adress as a where false limit 1 into message_row;
select array_to_json(array_agg(row_to_json(t)))
from (
select u.xwname,u.xwage,a.xwadress from table_users as u,table_adress as a where u.xwfid=a.xwfid
) t into msgjson;
OPEN a FOR select * from json_populate_recordset(null::message_row,msgjson);
--OPEN a FOR select msgjson as msgjson;
RETURN NEXT a;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
===========test:resulte
select * from get_jsonrows('a');
fetch all from a;
====data:
ERROR: type "message_row" does not exist
LINE 1: select * from json_populate_recordset(null::message_row,msgj...
^
QUERY: select * from json_populate_recordset(null::message_row,msgjson)
CONTEXT: PL/pgSQL function get_jsonrows(refcursor) line 12 at OPEN
********** Error **********
ERROR: type "message_row" does not exist
SQL state: 42704
Context: PL/pgSQL function get_jsonrows(refcursor) line 12 at OPEN
======================================================================
CREATE OR REPLACE FUNCTION get_jsonrows(a refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
msgjson character varying :='';
message_row record;
BEGIN
select u.xwname,u.xwage,a.xwadress from table_users as u,table_adress as a where false limit 1 into message_row;
select array_to_json(array_agg(row_to_json(t)))
from (
select u.xwname,u.xwage,a.xwadress from table_users as u,table_adress as a where u.xwfid=a.xwfid
) t into msgjson;
--OPEN a FOR select * from json_populate_recordset(null::message_row,msgjson);
--OPEN a FOR select msgjson as msgjson;
OPEN a FOR select message_row.xwname as xwname,message_row.xwage as xwage,message_row.xwadress as xwadress;
RETURN NEXT a;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
,论证结果为：no pass!!!!