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
>

Reply via email to