Ygthkb

How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?

How can I keep my gold safe from other PCs?

Is the UK legally prevented from having another referendum on Brexit?

Inversion applicable to three-dimensional lattices only

Including proofs of known theorems in master's thesis

Limits of Rolle theorem

Is the Maximum Use License for Everybody (MULE) a FOSS license?

In a post apocalypse world, with no power and few survivors, would Satnav still work?

Alternate timeline nomenclature

User input happy birthday program

How does a mid-19 century Military combat a modernized one?

If I tried and failed to start my own business, how do I apply for a job without job experience?

Finding the index of a specific element in a list

Trying to use an LCD 1602 to display, but I don't have a 10 KOhm potentiometer

What does an unprocessed RAW file look like?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

How to wrap a figure in exam document?

How long can the stop in a stop-and-go be?

How can I handle players killing my NPC outside of combat?

Why don't you get burned by the wood benches in a sauna?

Is there any way to play D&D without a DM?

Performance and power usage for Raspberry Pi in the Stratosphere

Disk space full during insert, what happens?

Can you help me solve this algebra problem?

postgres - function returning SET OF RECORD - error

returning set of records in stored procedureError: set_valued function called in context that cannot accept a set. What is it about?RETURN NEXT in Postgres FunctionPostgreSQL 9.5, getting “cached plan must not change result type” errorHow do you declare a set-returning-function to only be allowed in the FROM clause?Postgres full text search with better matchIs it possible to create a user defined type with plpgsql or SQL?Understanding Set Returning Function (SRF) in the SELECT ListPostgres: query on huge (11gb ) index does not returnDynamically define a RETURN table (column type, name) for subsequent loop

0

I created a function:

CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$

    DECLARE

        cup_ids int[] := null;

        sup_ids int[] := null;

        cup record;

        ous_ids_1 int[] := null;

        ous_ids_2 int[] := null;

        ous_ids int[] := null;

        scopes setof record;

    BEGIN

        select into cup * from company_companyuserprofile where id = uid;

        cup_ids := (select array(

          select distinct employee_id from company_managerrelation cm

          where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'

          union

          select distinct employee_id from company_dynamicrelation

          where target_id = uid

          ));

        sup_ids := (select array (

          select distinct supervisory_id::int from company_companyuserprofile cup

          where id = ANY(cup_ids)

          ));

        IF not cup.supervisory_id is null and cup.supervisory_id <> '' then

          ous_ids_1 := (select array (

            select distinct id from company_companyou where

            id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'



          ));

        end if;

        IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then

          ous_ids_2 := (select array (

            select distinct id from company_companyou where

            id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'

          ));

        end if;

        ous_ids := ous_ids_1 || ous_ids_2;

        scopes := (select a.id, a.title, b.type from

          company_companyou a left outer join company_companyoutype b on a.type_id = b.id

          where a.id = any(ous_ids) and b.type = 'Production Scope');

        return scopes;

    END;

$$ LANGUAGE plpgsql;



select get_managed_user_ids(26);

but I get this error:

[42601] ERROR: invalid type name "setof record"

I googled on the web but I haven't found an example to return a set of rows that fits my needs.

replacing return scopes; with return next scopes; or return query scopes; is not valid.

Anyone can help me on this?

postgresql plpgsql set-returning-functions

share|improve this question

asked 15 mins ago

LukeLuke

1011

add a comment | 

0

I created a function:

CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$

    DECLARE

        cup_ids int[] := null;

        sup_ids int[] := null;

        cup record;

        ous_ids_1 int[] := null;

        ous_ids_2 int[] := null;

        ous_ids int[] := null;

        scopes setof record;

    BEGIN

        select into cup * from company_companyuserprofile where id = uid;

        cup_ids := (select array(

          select distinct employee_id from company_managerrelation cm

          where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'

          union

          select distinct employee_id from company_dynamicrelation

          where target_id = uid

          ));

        sup_ids := (select array (

          select distinct supervisory_id::int from company_companyuserprofile cup

          where id = ANY(cup_ids)

          ));

        IF not cup.supervisory_id is null and cup.supervisory_id <> '' then

          ous_ids_1 := (select array (

            select distinct id from company_companyou where

            id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'



          ));

        end if;

        IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then

          ous_ids_2 := (select array (

            select distinct id from company_companyou where

            id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'

          ));

        end if;

        ous_ids := ous_ids_1 || ous_ids_2;

        scopes := (select a.id, a.title, b.type from

          company_companyou a left outer join company_companyoutype b on a.type_id = b.id

          where a.id = any(ous_ids) and b.type = 'Production Scope');

        return scopes;

    END;

$$ LANGUAGE plpgsql;



select get_managed_user_ids(26);

but I get this error:

[42601] ERROR: invalid type name "setof record"

I googled on the web but I haven't found an example to return a set of rows that fits my needs.

replacing return scopes; with return next scopes; or return query scopes; is not valid.

Anyone can help me on this?

postgresql plpgsql set-returning-functions

share|improve this question

asked 15 mins ago

LukeLuke

1011

add a comment | 

I created a function:

CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$

    DECLARE

        cup_ids int[] := null;

        sup_ids int[] := null;

        cup record;

        ous_ids_1 int[] := null;

        ous_ids_2 int[] := null;

        ous_ids int[] := null;

        scopes setof record;

    BEGIN

        select into cup * from company_companyuserprofile where id = uid;

        cup_ids := (select array(

          select distinct employee_id from company_managerrelation cm

          where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'

          union

          select distinct employee_id from company_dynamicrelation

          where target_id = uid

          ));

        sup_ids := (select array (

          select distinct supervisory_id::int from company_companyuserprofile cup

          where id = ANY(cup_ids)

          ));

        IF not cup.supervisory_id is null and cup.supervisory_id <> '' then

          ous_ids_1 := (select array (

            select distinct id from company_companyou where

            id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'



          ));

        end if;

        IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then

          ous_ids_2 := (select array (

            select distinct id from company_companyou where

            id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'

          ));

        end if;

        ous_ids := ous_ids_1 || ous_ids_2;

        scopes := (select a.id, a.title, b.type from

          company_companyou a left outer join company_companyoutype b on a.type_id = b.id

          where a.id = any(ous_ids) and b.type = 'Production Scope');

        return scopes;

    END;

$$ LANGUAGE plpgsql;



select get_managed_user_ids(26);

but I get this error:

[42601] ERROR: invalid type name "setof record"

I googled on the web but I haven't found an example to return a set of rows that fits my needs.

replacing return scopes; with return next scopes; or return query scopes; is not valid.

Anyone can help me on this?

postgresql plpgsql set-returning-functions

share|improve this question

asked 15 mins ago

LukeLuke

1011

CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$

    DECLARE

        cup_ids int[] := null;

        sup_ids int[] := null;

        cup record;

        ous_ids_1 int[] := null;

        ous_ids_2 int[] := null;

        ous_ids int[] := null;

        scopes setof record;

    BEGIN

        select into cup * from company_companyuserprofile where id = uid;

        cup_ids := (select array(

          select distinct employee_id from company_managerrelation cm

          where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'

          union

          select distinct employee_id from company_dynamicrelation

          where target_id = uid

          ));

        sup_ids := (select array (

          select distinct supervisory_id::int from company_companyuserprofile cup

          where id = ANY(cup_ids)

          ));

        IF not cup.supervisory_id is null and cup.supervisory_id <> '' then

          ous_ids_1 := (select array (

            select distinct id from company_companyou where

            id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'



          ));

        end if;

        IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then

          ous_ids_2 := (select array (

            select distinct id from company_companyou where

            id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'

          ));

        end if;

        ous_ids := ous_ids_1 || ous_ids_2;

        scopes := (select a.id, a.title, b.type from

          company_companyou a left outer join company_companyoutype b on a.type_id = b.id

          where a.id = any(ous_ids) and b.type = 'Production Scope');

        return scopes;

    END;

$$ LANGUAGE plpgsql;



select get_managed_user_ids(26);

[42601] ERROR: invalid type name "setof record"

share|improve this question

asked 15 mins ago

LukeLuke

1011

share|improve this question

asked 15 mins ago

LukeLuke

1011

asked 15 mins ago

LukeLuke

1011

asked 15 mins ago

LukeLuke

1011