Ygthkb

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

Possible issue with my W4 and tax return

Kernel and image of matrix: What are they? Why do they exist?

The No-Straight Maze

What is the draw frequency for 3 consecutive games (same players; amateur level)?

Identical projects by students at two different colleges: still plagiarism?

Buying a "Used" Router

Is there a non trivial covering of the Klein bottle by the Klein bottle

Is there a way to pause a running process on Linux systems and resume later?

Create linguistic diagram (in TikZ?)

Boss asked me to sign a resignation paper without a date on it along with my new contract

Rigorous justification for non-relativistic QM perturbation theory assumptions?

Is it possible to detect 100% of SQLi with a simple regex?

Why did Luke use his left hand to shoot?

How do you get out of your own psychology to write characters?

Why is it that Bernie Sanders is always called a "socialist"?

Can me and my friend spend the summer in Canada (6 weeks) at 16 years old without an adult?

Writing dialogues for characters whose first language is not English

Plausible reason for gold-digging ant

Website seeing facebook data from another site?

What is an efficient way to digitize a family photo collection?

Is it possible to rotate the Isolines on a Surface Using `MeshFunction`?

Insecure private-key encryption

Crack the bank account's password!

How to refer to individual columns of a record returned by a function?

access columns returned by psql from bashHow do I save functions to individual files in PostgreSQL?Concatenation of setof type or setof recordOrder of returned record with IN statement postgresaccessing fields of returned record objectHow to insert a record returned by a function into a tablePostgreSQL 9.5, getting “cached plan must not change result type” error“Lambda” function returns type string instead of type recordFunction returning table : access individual fieldsDynamically define a RETURN table (column type, name) for subsequent loop

3

I have a function that returns setof record type.

CREATE TYPE CIR_TYPE AS

(

    ID integer,

    path text,

    cycle boolean

);



CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $body$



DECLARE

    r CIR_TYPE;



BEGIN

    For r in WITH RECURSIVE graph(ID, path, cycle) AS (

                SELECT id AS id

                     , ARRAY[parentid, id] AS path

                     , (parentid = id) AS cycle    

                FROM  mytable



                UNION ALL



                SELECT d.id, d.parentid ||path, d.parentid = ANY(path)

                FROM   graph g

                JOIN   mytable d ON d.id = g.path[1]

                WHERE  NOT g.cycle

             )

             SELECT DISTINCT *

             FROM   graph

    Loop



        If r.cycle= TRUE then

        Return NEXT r;

        End if;



    End loop;



END;

$body$ LANGUAGE plpgsql;

When I execute the above function I get an output like below:

circular_ref

----------------------------

(0,"{1,0,1}",t)

(1,"{0,1,0}",t)

Here 0 and 1 are ID, {0,1,0} and {0,1,0} are path and t indicates cycle.

I want to print the output like

ID     |    Path

----       ------

0      |   1,0,1

1      |   0,1,0

Please suggest how to get the expected result.

postgresql postgresql-9.1

share|improve this question

edited Feb 13 '18 at 16:34

dezso

22.2k116096

asked May 14 '14 at 15:52

GaNeSh GoRdEGaNeSh GoRdE

464

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Unrelated, but: you don't really need a loop or PL/pgSQL here. A simple SQL query (or function) will do just fine
  
  – a_horse_with_no_name
  Feb 13 '18 at 20:04
  

  
  
  
  

add a comment | 

3

I have a function that returns setof record type.

CREATE TYPE CIR_TYPE AS

(

    ID integer,

    path text,

    cycle boolean

);



CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $body$



DECLARE

    r CIR_TYPE;



BEGIN

    For r in WITH RECURSIVE graph(ID, path, cycle) AS (

                SELECT id AS id

                     , ARRAY[parentid, id] AS path

                     , (parentid = id) AS cycle    

                FROM  mytable



                UNION ALL



                SELECT d.id, d.parentid ||path, d.parentid = ANY(path)

                FROM   graph g

                JOIN   mytable d ON d.id = g.path[1]

                WHERE  NOT g.cycle

             )

             SELECT DISTINCT *

             FROM   graph

    Loop



        If r.cycle= TRUE then

        Return NEXT r;

        End if;



    End loop;



END;

$body$ LANGUAGE plpgsql;

When I execute the above function I get an output like below:

circular_ref

----------------------------

(0,"{1,0,1}",t)

(1,"{0,1,0}",t)

Here 0 and 1 are ID, {0,1,0} and {0,1,0} are path and t indicates cycle.

I want to print the output like

ID     |    Path

----       ------

0      |   1,0,1

1      |   0,1,0

Please suggest how to get the expected result.

postgresql postgresql-9.1

share|improve this question

edited Feb 13 '18 at 16:34

dezso

22.2k116096

asked May 14 '14 at 15:52

GaNeSh GoRdEGaNeSh GoRdE

464

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Unrelated, but: you don't really need a loop or PL/pgSQL here. A simple SQL query (or function) will do just fine
  
  – a_horse_with_no_name
  Feb 13 '18 at 20:04
  

  
  
  
  

add a comment | 

I have a function that returns setof record type.

CREATE TYPE CIR_TYPE AS

(

    ID integer,

    path text,

    cycle boolean

);



CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $body$



DECLARE

    r CIR_TYPE;



BEGIN

    For r in WITH RECURSIVE graph(ID, path, cycle) AS (

                SELECT id AS id

                     , ARRAY[parentid, id] AS path

                     , (parentid = id) AS cycle    

                FROM  mytable



                UNION ALL



                SELECT d.id, d.parentid ||path, d.parentid = ANY(path)

                FROM   graph g

                JOIN   mytable d ON d.id = g.path[1]

                WHERE  NOT g.cycle

             )

             SELECT DISTINCT *

             FROM   graph

    Loop



        If r.cycle= TRUE then

        Return NEXT r;

        End if;



    End loop;



END;

$body$ LANGUAGE plpgsql;

When I execute the above function I get an output like below:

circular_ref

----------------------------

(0,"{1,0,1}",t)

(1,"{0,1,0}",t)

Here 0 and 1 are ID, {0,1,0} and {0,1,0} are path and t indicates cycle.

I want to print the output like

ID     |    Path

----       ------

0      |   1,0,1

1      |   0,1,0

Please suggest how to get the expected result.

postgresql postgresql-9.1

share|improve this question

edited Feb 13 '18 at 16:34

dezso

22.2k116096

asked May 14 '14 at 15:52

GaNeSh GoRdEGaNeSh GoRdE

464

CREATE TYPE CIR_TYPE AS

(

    ID integer,

    path text,

    cycle boolean

);



CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $body$



DECLARE

    r CIR_TYPE;



BEGIN

    For r in WITH RECURSIVE graph(ID, path, cycle) AS (

                SELECT id AS id

                     , ARRAY[parentid, id] AS path

                     , (parentid = id) AS cycle    

                FROM  mytable



                UNION ALL



                SELECT d.id, d.parentid ||path, d.parentid = ANY(path)

                FROM   graph g

                JOIN   mytable d ON d.id = g.path[1]

                WHERE  NOT g.cycle

             )

             SELECT DISTINCT *

             FROM   graph

    Loop



        If r.cycle= TRUE then

        Return NEXT r;

        End if;



    End loop;



END;

$body$ LANGUAGE plpgsql;

circular_ref

----------------------------

(0,"{1,0,1}",t)

(1,"{0,1,0}",t)

ID     |    Path

----       ------

0      |   1,0,1

1      |   0,1,0

share|improve this question

edited Feb 13 '18 at 16:34

dezso

22.2k116096

asked May 14 '14 at 15:52

GaNeSh GoRdEGaNeSh GoRdE

464

share|improve this question

edited Feb 13 '18 at 16:34

dezso

22.2k116096

asked May 14 '14 at 15:52

GaNeSh GoRdEGaNeSh GoRdE

464

edited Feb 13 '18 at 16:34

dezso

22.2k116096

edited Feb 13 '18 at 16:34

dezso

22.2k116096

asked May 14 '14 at 15:52

GaNeSh GoRdEGaNeSh GoRdE

464

asked May 14 '14 at 15:52

GaNeSh GoRdEGaNeSh GoRdE

464

1 Answer
1

active

oldest

votes

4

The problem, I guess, hits everyone starting with PostgreSQL. Fortunately, the solution is rather easy: simply call

SELECT * FROM circular_ref();

instead of

SELECT circular_ref();

This way it won't be a single record you get but a proper row. This way, you can even do

SELECT id, path FROM circular_ref();

to get the columns you want.

share|improve this answer

edited 5 mins ago

answered May 15 '14 at 14:48

dezsodezso

22.2k116096

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f65127%2fhow-to-refer-to-individual-columns-of-a-record-returned-by-a-function%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4

The problem, I guess, hits everyone starting with PostgreSQL. Fortunately, the solution is rather easy: simply call

SELECT * FROM circular_ref();

instead of

SELECT circular_ref();

This way it won't be a single record you get but a proper row. This way, you can even do

SELECT id, path FROM circular_ref();

to get the columns you want.

share|improve this answer

edited 5 mins ago

answered May 15 '14 at 14:48

dezsodezso

22.2k116096

add a comment | 

4

The problem, I guess, hits everyone starting with PostgreSQL. Fortunately, the solution is rather easy: simply call

SELECT * FROM circular_ref();

instead of

SELECT circular_ref();

This way it won't be a single record you get but a proper row. This way, you can even do

SELECT id, path FROM circular_ref();

to get the columns you want.

share|improve this answer

edited 5 mins ago

answered May 15 '14 at 14:48

dezsodezso

22.2k116096

add a comment | 

The problem, I guess, hits everyone starting with PostgreSQL. Fortunately, the solution is rather easy: simply call

SELECT * FROM circular_ref();

instead of

SELECT circular_ref();

This way it won't be a single record you get but a proper row. This way, you can even do

SELECT id, path FROM circular_ref();

to get the columns you want.

share|improve this answer

edited 5 mins ago

answered May 15 '14 at 14:48

dezsodezso

22.2k116096

SELECT * FROM circular_ref();

SELECT circular_ref();

SELECT id, path FROM circular_ref();

share|improve this answer

edited 5 mins ago

answered May 15 '14 at 14:48

dezsodezso

22.2k116096

answered May 15 '14 at 14:48

dezsodezso

22.2k116096

answered May 15 '14 at 14:48

dezsodezso

22.2k116096