Code: Select all
CREATE OR REPLACE FUNCTION dajmiesieczneodpisyzarok(
IN rok character varying,
IN id_srodektrwaly integer)
RETURNS TABLE(id_srodektrwaly numeric, styczen numeric, luty numeric, marzec numeric, kwiecien numeric, maj numeric, czerwiec numeric, lipiec numeric, sierpien numeric, wrzesien numeric, pazdziernik numeric, listopad numeric, grudzien numeric) AS
$BODY$ SELECT * FROM crosstab( 'select id_srodektrwaly::numeric,miesiac::numeric,miesodpis::numeric from planamortyzacji where rok='''||$1||''' and id_srodektrwaly='''||$2||''' order by id_srodektrwaly') AS final_result(id_srodektrwaly numeric,m01 numeric,m02 numeric,m03 numeric,m04 numeric,m05 numeric,m06 numeric,m07 numeric,m08 numeric,m09 numeric,m10 numeric,m11 numeric,m12 numeric); $BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION dajmiesieczneodpisyzarok(character varying, integer)
OWNER TO wij;
Delphi code
Code: Select all
var
Query: TZQuery;
begin
Query := TZQuery.Create(nil);
try
Query.Connection := ZQuery1.Connection;
Query.SQL.Text := 'select exists(select * from pg_proc where proname = ''dajmiesieczneodpisyzarok'');';
Query.Open;
if not Query.FieldByName('exists').AsBoolean then
begin
Query.Close;
Query.SQL.Clear;
Query.SQL.add('CREATE OR REPLACE FUNCTION dajmiesieczneodpisyzarok( ');
Query.SQL.add(' IN rok character varying, ');
Query.SQL.add(' IN id_srodektrwaly integer) ');
Query.SQL.add(' RETURNS TABLE(id_srodektrwaly numeric,styczeń numeric,luty numeric,marzec numeric,');
Query.SQL.add('kwiecień numeric,maj numeric,czerwiec numeric,lipiec numeric,sierpień numeric,wrzesień numeric,październik numeric,listopad numeric,grudzień numeric) AS ');
Query.SQL.add(' $BODY$ ');
Query.SQL.add(' SELECT * ');
Query.SQL.add(' FROM crosstab( ''select id_srodektrwaly::numeric,miesiac::numeric,miesodpis::numeric from planamortyzacji where rok=''''2017'''' and id_srodektrwaly=16 order by id_srodektrwaly');
Query.SQL.add(' AS final_result(id_srodektrwaly numeric,m1 numeric,m2 numeric,m3 numeric,m4 numeric,m5 numeric,m6 numeric,m7 numeric,m8 numeric,m9 numeric,10 numeric,11 numeric,12 numeric); ');
Query.SQL.add(' $BODY$ ');
Query.SQL.add(' LANGUAGE sql VOLATILE ');
Query.SQL.add(' COST 100 ');
Query.SQL.add(' ROWS 1000; ');
Query.SQL.add(' ALTER FUNCTION dajmiesieczneodpisyzarok(character varying, integer) ');
Query.SQL.add(' OWNER TO wij; ');
Query.ExecSQL;
end;
finally
FreeAndNil(Query);
end;
end;
Unfortunately, I have a problem to type it into the database using Zeos
No matter if I type it directly
Query.SQl.text: = '...';
Or using the resource string
resourcestring
sql = '...';
Query.SQL.Text: = sql;
Or using
Query.Close;
Query.SQL.Clear;
Query.SQL.add ('...');
I still get Access Violation
this
Query.SQL.add ('SELECT * FROM scheduling;');
Of course, it does not make mistakes.
I buried a little I followed
And crashes into a function
Function TZTokenizer.TokenizeStreamToList (Stream: TStream;
Options: TZTokenOptions): TStrings;
Has a problem with the sign ::
this
FROM crosstab ('select id_srodektrwaly :: numeric ...
I changed
on
Query.SQL.add ('FROM crosstab (' 'select');
Query.SQL.add ('CAST (id_srodektrwaly AS numeric)');
...
I'm not lecturing here but still can not handle SQL in ExecSQL
The problem is that around the Unicode and the string break on the Token may not cope with the "nested" SQL.
How to deal with this?
The best thing is that if I put Query on the form and execute SQL it is all right.
