Ygthkb

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

What if you do not believe in the project benefits?

A cancellation property for permutations?

How can I portray body horror and still be sensitive to people with disabilities?

SQL Server 2017 crashes when backing up because filepath is wrong

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

Can someone explain the need for perturbation theory in QM?

Exploding Numbers

Is there any danger of my neighbor having my wife's signature?

How many copper coins fit inside a cubic foot?

What's the meaning of #0?

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

Have any astronauts or cosmonauts died in space?

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

How bad is a Computer Science course that doesn't teach Design Patterns?

Is it Safe to Plug an Extension Cord Into a Power Strip?

Sets which are both Sum-free and Product-free.

Draw triangle with text in vertices/edges

Can a planet be tidally unlocked?

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

How can changes in personality/values of a person who turned into a vampire be explained?

Can I do anything else with aspersions other than cast them?

How does holding onto an active but un-used credit card affect your ability to get a loan?

Why write a book when there's a movie in my head?

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♦ 4 mins 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♦ 4 mins 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