can't support function returns setof record type

tonyhsieh6447 · Post by **tonyhsieh6447** » Thu 13 May 2010 13:40

my postgresql function code follow:
CREATE OR REPLACE FUNCTION "public"."gettitle" (
"code" uuid
)
RETURNS SETOF record AS
$body$
DECLARE
r record;
BEGIN
FOR r in
select * from
dblink('dbname=LMSVP','SELECT
public."TTitles"."CTitleCode",
public."TTitles"."CTWName" AS ctitlename,
public."TArtists"."CTWName" AS cartistname,
public."TLabels"."CTWName" AS clabelname,
public."TSongs"."CSongCode",
public."TSongs"."CTWName" AS csongname,
public."TSongs"."CRecordingMasterMP3Duration" AS csongduration
FROM
public."TTitleSong"
INNER JOIN public."TSongArtist" ON (public."TTitleSong"."CSongCode" = public."TSongArtist"."CSongCode")
INNER JOIN public."TSongs" ON (public."TTitleSong"."CSongCode" = public."TSongs"."CSongCode")
INNER JOIN public."TArtists" ON (public."TSongArtist"."CArtistCode" = public."TArtists"."CArtistCode")
INNER JOIN public."TTitles" ON (public."TTitleSong"."CTitleCode" = public."TTitles"."CTitleCode")
INNER JOIN public."TLabels" ON (public."TTitles"."CLabelCode" = public."TLabels"."CLabelCode")
WHERE
public."TTitles"."CTitleCode" = ' || quote_literal($1) ||
'ORDER BY
public."TSongs"."CDiskNumber",
public."TSongs"."CTrack"')
as (CTitleCode uuid,ctitlename VARCHAR,cartistname VARCHAR,clabelname VARCHAR,CSongCode uuid,csongname VARCHAR,csongduration INTEGER)
loop
return next r;
end loop;
RETURN;
END;
$body$
LANGUAGE 'plpgsql'

i can't use LINQ to SQL Model & Entity Framework Model Designer Generate Correct return of the Schema.And I try to use the DataSet has no way to generate the correct return of the Schema.
Can help me. How I should do. Or should I go to rewrite my postgresql Function.

AndreyR · Post by **AndreyR** » Wed 19 May 2010 14:36

Could you please provide the error message and the callstack of the error you obtain in the LINQ to PostgreSQL model?

tonyhsieh6447 · Post by **tonyhsieh6447** » Mon 24 May 2010 12:02

i get error message and the callstack of the error follow:
Inner Exception:{"a column definition list is required for functions returning \"record\""}
Message :Error on executing DbCommand.
Stack Trace:
at Devart.Data.Linq.LinqCommandExecutionException.a(String A_0, Exception A_1)
at Devart.Data.Linq.Provider.DataProvider.a(b A_0, Object[] A_1, Object[] A_2, Object A_3)
at Devart.Data.Linq.Provider.DataProvider.a(b A_0, Object[] A_1)
at Devart.Data.Linq.Provider.DataProvider.b.a(b A_0, Object[] A_1)
at Devart.Data.Linq.DataQuery`1.i()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
If Postgresql have to define a type and then return this type of other Record will not go wrong.

AndreyR · Post by **AndreyR** » Thu 27 May 2010 13:32

I have created a simple function with Join that returns a type that does not coincide with any table.
I've got this error while executing "select * from getsample()" in pgAdmin.
Could you please send us (support * devart * com, subject "LINQ: column definition for RECORD") a small test project including the working sample in Entity Framework and the sample that is not working with LINQ to SQL?
Please also include the script of database objects.