can't support function returns setof record type
Posted: 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.
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.