Adding postgresql function in Devart Entity Model

Question

I'm trying to import a postgresql function in an devart entity model, where I want to return a "custom" table, but I just can't get it working

I define my function like this:

CREATE OR REPLACE FUNCTION "GetJournalEntriesByVoucherId"(bigint)
  RETURNS SETOF record AS
$BODY$
SELECT 
    JE."JournalEntryID"
    JE."Amount",
    JE."EntryText",
FROM
    "JournalEntries" AS JE
WHERE
    JE."FK_Voucher"=$1
$BODY$
  LANGUAGE sql VOLATILE STRICT
  COST 100
  ROWS 1000;
ALTER FUNCTION getjournalentriesbyvoucherid(bigint) OWNER TO sqluser;

And used the steps in post #2 in http://www.devart.com/forums/viewtopic.php?p=71252 but I get the error message

The data reader is incompatible with the specified Model.Entity1. A member of the type, JournalEntryID, does not have a corresponding column in the data reader with the same name"

Does the SELECT query work if you execute it directly, without the stored procedure? — Jonathan Hall, Jul 11 '11 at 08:15
Do you get this error when defining the stored procedure, or when calling it? If when calling it, can you paste the exact query you're executing that calls the stored procedure? — Jonathan Hall, Jul 11 '11 at 08:54
When calling it, and im using the entity framework to call it, so i do not have a "calling query" — Torben Pi Jensen, Jul 11 '11 at 09:20

Jonathan Hall · Accepted Answer · 2011-07-11T09:06:59.253

1

I've often had better luck using OUT variables for set-returning stored procedures. I don't know if this will help in your case, but you might try it. I honestly don't know if this changes the way the function works or just the way it's defined...

CREATE OR REPLACE FUNCTION "GetJournalEntriesByVoucherId"(BIGINT,
    JournalEntryID OUT DATATYPE,
    Amount OUT INT,
    EntryText OUT VARCHAR,
)
RETURNS SETOF record AS
$BODY$
....

edited Jul 11 '11 at 09:06

answered Jul 11 '11 at 08:58

Jonathan Hall

75,165
16
143
189

Well, it changed the way the import recognizes the return type(it is now "correct") but I still get the data reader error. – Torben Pi Jensen Jul 11 '11 at 09:06
Funny thing is that if I keep a return entity with a field Returnvalue of the type string it actually returns the rows in objects where returnvalue will be "(JournalEntryID.value,Amount.value,EntryText.value)" as a string... – Torben Pi Jensen Jul 11 '11 at 09:08
I managed to solve my problem by creating a custom type, which had the same fields as the result of my query and let the result of the function be "setof theType" which then made the data reader work. I will post an answer in 4 hours when I am allow to do so – Torben Pi Jensen Jul 11 '11 at 12:02
@plazm: If you solved your own question, you should create an answer to your question, then accept it. – Jonathan Hall Jul 13 '11 at 20:04

score 0 · Answer 2 · answered Jul 13 '11 at 12:45

@Flimzy is correct.
When a function returns setof record the reader returned from the function contains the record in form '(field1.Value, ..., fieldk.Value)' and is treated as varchar.
When out parameters are specified, the names and types of these parameters let the server know what the fields are in the reader, and the values are returned correctly. We have fixed some errors in this scenario in the latest 5.30.180 build, it works now.
The solution with strongly-typed setof (CompositeType) is an appropriate one as well.

Adding postgresql function in Devart Entity Model

2 Answers2