Hello,
The patch allows to use this ... :
select row_to_json(row(1,'foo'))
... instead of this (in this case, the ::text cast avoids the "Missing (compatible) underlying dataset, can not open" error):
select row_to_json(row(1,'foo'))::text
--- result of both ---
{ "f1" : 1, "f2" : "foo" }
It worked fine too:
drop table if exists person cascade;
create table person (id serial not null primary key,
name varchar(50) not null unique
); insert into person (name) values ('Foo');
insert into person (name) values ('Bar');...
select array_to_json(array_agg(row_to_json(t)))
from (
select id, name from person
) t
--- result ---
[{ "id" : 1, "name" : "Foo" }, { "id" : 2, "name" : "Bar" }]
=== begin patch ===
From 079807a984a0f414860e599c8836c9f099add46c Mon Sep 17 00:00:00 2001
Earlier message 107 lines
From: silvioprog
Date: Wed, 4 Mar 2015 22:10:48 -0300
Subject: [PATCH 1/1] fcl-db: postgresql: add support for JSON.
---
packages/fcl-db/src/sqldb/postgres/pqconnection.pp | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
b/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
index bcf4382..bdf036d 100644
--- a/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
+++ b/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
@@ -176,6 +176,7 @@ const Oid_Bool = 16;
Oid_Int8 = 20;
Oid_int2 = 21;
Oid_Int4 = 23;
+ Oid_JSON = 114;
Oid_Float4 = 700;
Oid_Money = 790;
Oid_Float8 = 701;
@@ -736,7 +737,7 @@ begin
ATypeOID:=0;
AOID:=PQftype(res,Tuple);
case AOID of
- Oid_varchar,Oid_bpchar,
+ Oid_varchar,Oid_bpchar,Oid_JSON,
Oid_name : begin
Result := ftstring;
size := PQfsize(Res, Tuple);
--
1.9.5.msysgit.0
=== end patch ===
== begin test ==
program project1;
{$mode objfpc}{$H+}
uses
pqconnection,
sqldb,
jsonparser,
fpjson;
procedure ValidateJson(const S: string);
var
j: TJSONData;
begin
// just to validade the JSON result
with TJSONParser.Create(S) do
try
j := Parse;
try
// WriteLn(j.AsJSON); uncomment to print the JSON resut
finally
j.Free;
end;
finally
Free;
end;
end;
var
q: TSQLQuery;
con: TPQConnection;
begin
con := TPQConnection.Create(nil);
con.Transaction := TSQLTransaction.Create(con);
q := TSQLQuery.Create(con);
try
con.HostName := '127.0.0.1';
con.DatabaseName := 'postgres';
con.UserName := 'postgres';
con.Password := 'postgres';
q.Transaction := con.Transaction;
q.SQL.Text := 'select row_to_json(row(1,''foo''))';
q.Open;
while not q.EOF do
begin
ValidateJson(q.Fields[0].AsString);
q.Next;
end;
finally
con.Free;
end;
end.
== end test ==
P.S.: Get the 114 type via:
$ psql -qAt -F $'\t' -p 5432 postgres -c
$ "select 'Oid_' || upper(typname), '=' || oid from pg_type"
A: why get it as string instead of memo or blob?
R: the string it is easy to be parsed, converted to other sources (eg:
stream), and internally the FCL-JSON uses a stringlist as JSON data source,
so the string type could be the best type for JSON representation.
Thank you!
--
Silvio Clécio
My public projects - github.com/silvioprog