can't support function returns setof record type

can't support function returns setof record type

Postby 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.
tonyhsieh6447
 
Posts: 3
Joined: Tue 23 Mar 2010 16:54
Location: Taiwan

Postby 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?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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.
tonyhsieh6447
 
Posts: 3
Joined: Tue 23 Mar 2010 16:54
Location: Taiwan

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL