Sfrgttk

Do these large-scale, human power-plant-tending robots from the Matrix movies have a name, in-universe or out?

Spells that would be effective against a Modern Day army but would NOT destroy a fantasy one

Are all power cords made equal?

Badly designed reimbursement form. What does that say about the company?

How Create a list of the first 10,000 digits of Pi and sum it?

Add number in the string after each letter

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

Can a Hydra make multiple opportunity attacks at once?

Why don't programs completely uninstall (remove all their files) when I remove them?

What does "don't have a baby" imply or mean in this sentence?

Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once

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

Are there any rules for handling distractions whilst performing skill checks?

Minimum Viable Product for RTS game?

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

bash aliases do not expand even with shopt expand_aliases

Why do single electrical receptacles exist?

Short story about a man betting a group he could tell a story, and one of them would disappear and the others would not notice

How do I avoid the "chosen hero" feeling?

Is the tritone (A4 / d5) still banned in Roman Catholic music?

Why and/or operations in python statement are behaving unexpectedly?

What happens if both players misunderstand the game state until it's too late?

Can you say "leftside right"?

What does an unprocessed RAW file look like?

PostgreSQL function that returns a 'casted' complex type from query

stored procedure returns ERROR 1305 (42000): FUNCTION does not existConcatenation of setof type or setof recordIn PostgreSQL, is there a type-safe first() aggregate function?Postgresql - Error in function that returns tablePostgreSQL 9.5, getting “cached plan must not change result type” errorIs it possible to create a user defined type with plpgsql or SQL?PostgreSQL query is slow when return LineString dataPostgreSQL 9.5 query performance depends on JOINed column in SELECT clauseDynamically define a RETURN table (column type, name) for subsequent loopCustom return type of a function in Oracle PL SQl

1

I tried to define a PostgreSQL function that returns a complex type from a query via:

CREATE TYPE last_contract AS (

    contract_id bigint,

    contract_date timestamp

);

and

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) RETURNS last_contract AS $func$

DECLARE ret last_contract;

BEGIN



    SELECT DISTINCT ON (tdf.delivery_id) ROW(tcf.contract_id, tcf.date)::last_contract INTO ret

        FROM task_delivery_fetch tdf 

        INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

        WHERE tdf.delivery_id = delivery

        ORDER BY tdf.delivery_id, tcf.contract_id DESC

        LIMIT 1;





    RETURN ret;



END;

$func$ LANGUAGE plpgsql;

sadly, all I get is an error:

SELECT delivery_last_contract(12197);                                                                                                                                                                                                                                   ERROR:  invalid input syntax for integer: "(13605,"2016-12-06 00:00:00+01")"

CONTEXT:  PL/pgSQL function delivery_last_contract(bigint) line 5 at SQL statement

isn't there a way to actually return a single complex type returned from a query?

postgresql functions

share|improve this question

asked Feb 17 '17 at 14:22

Christian SchmittChristian Schmitt

186110

bumped to the homepage by Community♦ 34 secs ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Could you please add the actual error message? One or two lines are missing. In any case, DISTINCT ON feels unnecessary with ORDER BY ... LIMIT 1.
  
  – dezso
  Feb 17 '17 at 14:32
  
  
  

  
  
  
  

add a comment | 

1

I tried to define a PostgreSQL function that returns a complex type from a query via:

CREATE TYPE last_contract AS (

    contract_id bigint,

    contract_date timestamp

);

and

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) RETURNS last_contract AS $func$

DECLARE ret last_contract;

BEGIN



    SELECT DISTINCT ON (tdf.delivery_id) ROW(tcf.contract_id, tcf.date)::last_contract INTO ret

        FROM task_delivery_fetch tdf 

        INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

        WHERE tdf.delivery_id = delivery

        ORDER BY tdf.delivery_id, tcf.contract_id DESC

        LIMIT 1;





    RETURN ret;



END;

$func$ LANGUAGE plpgsql;

sadly, all I get is an error:

SELECT delivery_last_contract(12197);                                                                                                                                                                                                                                   ERROR:  invalid input syntax for integer: "(13605,"2016-12-06 00:00:00+01")"

CONTEXT:  PL/pgSQL function delivery_last_contract(bigint) line 5 at SQL statement

isn't there a way to actually return a single complex type returned from a query?

postgresql functions

share|improve this question

asked Feb 17 '17 at 14:22

Christian SchmittChristian Schmitt

186110

bumped to the homepage by Community♦ 34 secs ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Could you please add the actual error message? One or two lines are missing. In any case, DISTINCT ON feels unnecessary with ORDER BY ... LIMIT 1.
  
  – dezso
  Feb 17 '17 at 14:32
  
  
  

  
  
  
  

add a comment | 

I tried to define a PostgreSQL function that returns a complex type from a query via:

CREATE TYPE last_contract AS (

    contract_id bigint,

    contract_date timestamp

);

and

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) RETURNS last_contract AS $func$

DECLARE ret last_contract;

BEGIN



    SELECT DISTINCT ON (tdf.delivery_id) ROW(tcf.contract_id, tcf.date)::last_contract INTO ret

        FROM task_delivery_fetch tdf 

        INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

        WHERE tdf.delivery_id = delivery

        ORDER BY tdf.delivery_id, tcf.contract_id DESC

        LIMIT 1;





    RETURN ret;



END;

$func$ LANGUAGE plpgsql;

sadly, all I get is an error:

SELECT delivery_last_contract(12197);                                                                                                                                                                                                                                   ERROR:  invalid input syntax for integer: "(13605,"2016-12-06 00:00:00+01")"

CONTEXT:  PL/pgSQL function delivery_last_contract(bigint) line 5 at SQL statement

isn't there a way to actually return a single complex type returned from a query?

postgresql functions

share|improve this question

asked Feb 17 '17 at 14:22

Christian SchmittChristian Schmitt

186110

CREATE TYPE last_contract AS (

    contract_id bigint,

    contract_date timestamp

);

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) RETURNS last_contract AS $func$

DECLARE ret last_contract;

BEGIN



    SELECT DISTINCT ON (tdf.delivery_id) ROW(tcf.contract_id, tcf.date)::last_contract INTO ret

        FROM task_delivery_fetch tdf 

        INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

        WHERE tdf.delivery_id = delivery

        ORDER BY tdf.delivery_id, tcf.contract_id DESC

        LIMIT 1;





    RETURN ret;



END;

$func$ LANGUAGE plpgsql;

SELECT delivery_last_contract(12197);                                                                                                                                                                                                                                   ERROR:  invalid input syntax for integer: "(13605,"2016-12-06 00:00:00+01")"

CONTEXT:  PL/pgSQL function delivery_last_contract(bigint) line 5 at SQL statement

share|improve this question

asked Feb 17 '17 at 14:22

Christian SchmittChristian Schmitt

186110

share|improve this question

asked Feb 17 '17 at 14:22

Christian SchmittChristian Schmitt

186110

asked Feb 17 '17 at 14:22

Christian SchmittChristian Schmitt

186110

asked Feb 17 '17 at 14:22

Christian SchmittChristian Schmitt

186110

1 Answer
1

active

oldest

votes

0

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) 

                   RETURNS last_contract AS $func$

  DECLARE ret last_contract;

  BEGIN

    SELECT tcf.contract_id, tcf.date

      INTO ret

      FROM task_delivery_fetch tdf 

      INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

     WHERE tdf.delivery_id = delivery

     ORDER BY tdf.delivery_id, tcf.contract_id DESC

     LIMIT 1;

    RETURN ret;    

  END;

$func$ LANGUAGE plpgsql;

works. Simpler is better.

share|improve this answer

edited Feb 18 '17 at 22:37

answered Feb 17 '17 at 15:36

DarioDario

638312

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%2f164686%2fpostgresql-function-that-returns-a-casted-complex-type-from-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) 

                   RETURNS last_contract AS $func$

  DECLARE ret last_contract;

  BEGIN

    SELECT tcf.contract_id, tcf.date

      INTO ret

      FROM task_delivery_fetch tdf 

      INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

     WHERE tdf.delivery_id = delivery

     ORDER BY tdf.delivery_id, tcf.contract_id DESC

     LIMIT 1;

    RETURN ret;    

  END;

$func$ LANGUAGE plpgsql;

works. Simpler is better.

share|improve this answer

edited Feb 18 '17 at 22:37

answered Feb 17 '17 at 15:36

DarioDario

638312

add a comment | 

0

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) 

                   RETURNS last_contract AS $func$

  DECLARE ret last_contract;

  BEGIN

    SELECT tcf.contract_id, tcf.date

      INTO ret

      FROM task_delivery_fetch tdf 

      INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

     WHERE tdf.delivery_id = delivery

     ORDER BY tdf.delivery_id, tcf.contract_id DESC

     LIMIT 1;

    RETURN ret;    

  END;

$func$ LANGUAGE plpgsql;

works. Simpler is better.

share|improve this answer

edited Feb 18 '17 at 22:37

answered Feb 17 '17 at 15:36

DarioDario

638312

add a comment | 

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) 

                   RETURNS last_contract AS $func$

  DECLARE ret last_contract;

  BEGIN

    SELECT tcf.contract_id, tcf.date

      INTO ret

      FROM task_delivery_fetch tdf 

      INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

     WHERE tdf.delivery_id = delivery

     ORDER BY tdf.delivery_id, tcf.contract_id DESC

     LIMIT 1;

    RETURN ret;    

  END;

$func$ LANGUAGE plpgsql;

works. Simpler is better.

share|improve this answer

edited Feb 18 '17 at 22:37

answered Feb 17 '17 at 15:36

DarioDario

638312

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) 

                   RETURNS last_contract AS $func$

  DECLARE ret last_contract;

  BEGIN

    SELECT tcf.contract_id, tcf.date

      INTO ret

      FROM task_delivery_fetch tdf 

      INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id 

     WHERE tdf.delivery_id = delivery

     ORDER BY tdf.delivery_id, tcf.contract_id DESC

     LIMIT 1;

    RETURN ret;    

  END;

$func$ LANGUAGE plpgsql;

share|improve this answer

edited Feb 18 '17 at 22:37

answered Feb 17 '17 at 15:36

DarioDario

638312

answered Feb 17 '17 at 15:36

DarioDario

638312

answered Feb 17 '17 at 15:36

DarioDario

638312