imported>Pavel v 12. 2. 2010, 22:08

2010-02-12T22:08:45Z

Nová stránka

<pre>
CREATE OR REPLACE FUNCTION fx()
RETURNS int AS
$$
BEGIN
DECLARE a,b int;
EXECUTE IMMEDIATE 'SELECT 10,20' INTO a,b;
PRINT a,b;
RETURN a+b;
END;
$$ LANGUAGE plpgpsm;
</pre>
''Syntax''
<pre>
EXECUTE IMMEDIATE vyraz [INTO (variables list| RECCORD or ROW variable)] [USING variables list]
</pre>

<pre>
CREATE OR REPLACE FUNCTION fxx(a int)
RETURNS float AS
$$
BEGIN
DECLARE f float;
PREPARE prep(float) from 'select $1';
EXECUTE prep INTO f USING a;
RETURN f ;
END;
$$ LANGUAGE plpgpsm;
CREATE FUNCTION
</pre>
''Syntax''
<pre>
PREPARE name [ '(' types list ')' ] FROM expression

EXECUTE name [INTO (variables list | RECORD or ROW expression)]
[USING variables list]
</pre>

<pre>
CREATE OR REPLACE FUNCTION fx(a int)
RETURNS int AS
$$
#option dump
BEGIN
DECLARE x, y integer;
DECLARE SQLSTATE char(5);

DECLARE cx CURSOR FOR prepstmt;

PREPARE prepstmt(int) FROM 'SELECT a*100, b*100 FROM Foo WHERE Foo.a = $1';

OPEN cx USING a;

FETCH cx INTO x, y;
WHILE SQLSTATE = '00000' DO
PRINT x, y;
FETCH cx INTO x, y;
END WHILE;

CLOSE cx;
RETURN a;
END;
$$ LANGUAGE plpgpsm;
</pre>

<pre>
--dangerous, variables _tab and _var are security risk
EXECUTE IMMEDIATE 'SELECT * FROM '|| _tab || ' WHERE a = ''' || _var ||''' INTO _a, _b;

-- less security risk, _tab cannot contains special chars.
-- only _tabs is security risk
PREPARE psel(varchar) 'SELECT * FROM ' || _tab || 'WHERE a = ?';
EXECUTE psel INTO _a, _b USING _var;
</pre>

Dynamics SQL - Historie editací

imported>Pavel v 12. 2. 2010, 22:08