I have already attempted a similar approach and I could not find a way to pass the outer value of the VM ID to the inner SELECT. For example:
SELECT row.snt_code AS "snt_code", row.vdc AS "vdc", row.uuid AS "uuid", row_to_json(row, true)::json AS "json" FROM ( SELECT vm.*, CONCAT('https://int.cloudcontrol.sgns.net/v3/customer/', vm.snt_code, '/vdc/', vm.vdc, '/vm/', vm.uuid) AS "self", 'cc.v3.sungardas.vm' AS "type", (SELECT DISTINCT * FROM virtual_interfaces WHERE vmid=*vm.id <http://vm.id>*) as interfaces FROM virtual_machines vm ) row; Placing the vm.id value there for the WHERE clause gives the error: SQL Error [42703]: ERROR: column vm.id does not exist Position: 351 ERROR: column vm.id does not exist Position: 351 Is there some way to make that value available to the inner select? Thanks in advance! Deven On Mon, Mar 30, 2015 at 5:46 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Mon, Mar 30, 2015 at 4:22 PM, Deven Phillips > <deven.phill...@gmail.com> wrote: > > I'm using PostgreSQL 9.4.1 on Ubuntu 14.10. > > > > The function does the following: > > > > DROP FUNCTION get_vm_with_interfaces(vm_id BIGINT); > > > > CREATE OR REPLACE FUNCTION get_virtual_interfaces(vm_id BIGINT) RETURNS > > jsonb AS $$ > > DECLARE > > res jsonb; > > BEGIN > > SELECT array_to_json(array_agg(row_to_json(i, true)), true) > > FROM ( > > SELECT DISTINCT * FROM virtual_interfaces WHERE vmid=vm_id) i > > INTO res; > > RETURN res; > > END; > > $$ LANGUAGE PLPGSQL; > > please, try to refrain from top posting. particularly with emails > like this where the context of the question is important. Anyways, > your inner function could be trivially inlined as so: > > SELECT row_to_json(row) AS json > FROM ( > SELECT > c.snt_code AS "snt_code", > vdc.id AS "vdc", > vm.uuid AS "uuid", > vm.name AS "name", > vm.os AS "os", > vm.service_type AS "service_type", > vm.template_name AS "template_name", > ( -- get_vm_with_interfaces(vm.id) > SELECT array_to_json(array_agg(row_to_json(i, true)), true) > FROM ( > SELECT DISTINCT * FROM virtual_interfaces vi WHERE vmid=vm_id > ) i > ) as interfaces > FROM liquorstore_customer c > LEFT JOIN liquorstore_virtualdatacenter vdc ON c.id=vdc.customer_id > LEFT JOIN liquorstore_virtualmachine vm ON vm.virtual_data_center_id= > vdc.id > WHERE c.snt_code='abcd' AND vdc.id=111 AND > vm.uuid='422a141f-5e46-b0f2-53b8-e31070c883ed' > ) row > > I would personally simplify the subquery portion to: > ( -- get_vm_with_interfaces(vm.id) > SELECT array_agg(i) > FROM ( > SELECT DISTINCT * FROM virtual_interfaces vi WHERE vmid=vm_id > ) i > > , allowing for the outer 'to_json' to handle the final > transformation. I'm not going to do it for you, but you could > probably simplify the query even further by moving the aggregation out > of a correlated subquery and into the basic field list, which would be > faster for certain distributions of data. > > Also, a note about jsonb, which you used inside the inner function. > jsonb is much better than type 'json' for any case involving > manipulation of the json, searching, or repeated sub-document > extraction. However, for serialization to an application, it is > basically pessimal as it involves building up internal structures that > the vanilla json type does not involve. The basic rule of thumb is: > serialization, json, everything else, jsonb. > > merlin >