PostgreSQL function that returns a 'casted' complex type from querystored procedure returns ERROR 1305...
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
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
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.
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
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 withORDER 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
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
postgresql functions
asked Feb 17 '17 at 14:22
Christian SchmittChristian Schmitt
186110
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.
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 withORDER BY ... LIMIT 1
.
– dezso
Feb 17 '17 at 14:32
add a comment |
3
Could you please add the actual error message? One or two lines are missing. In any case,DISTINCT ON
feels unnecessary withORDER BY ... LIMIT 1
.
– dezso
Feb 17 '17 at 14:32
3
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
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 Answer
1
active
oldest
votes
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.
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
});
}
});
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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.
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.
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.
edited Feb 18 '17 at 22:37
answered Feb 17 '17 at 15:36
DarioDario
638312
638312
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Could you please add the actual error message? One or two lines are missing. In any case,
DISTINCT ON
feels unnecessary withORDER BY ... LIMIT 1
.– dezso
Feb 17 '17 at 14:32