http://postgres.cz/index.php?title=Dynamics_SQL&feed=atom&action=history
Dynamics SQL - Historie editací
2024-03-29T01:37:05Z
Historie editací této stránky
MediaWiki 1.36.0
http://postgres.cz/index.php?title=Dynamics_SQL&diff=250&oldid=prev
imported>Pavel v 12. 2. 2010, 22:08
2010-02-12T22:08:45Z
<p></p>
<p><b>Nová stránka</b></p><div><pre><br />
CREATE OR REPLACE FUNCTION fx() <br />
RETURNS int AS <br />
$$<br />
BEGIN <br />
DECLARE a,b int; <br />
EXECUTE IMMEDIATE 'SELECT 10,20' INTO a,b; <br />
PRINT a,b; <br />
RETURN a+b; <br />
END; <br />
$$ LANGUAGE plpgpsm;<br />
</pre><br />
''Syntax''<br />
<pre><br />
EXECUTE IMMEDIATE vyraz [INTO (variables list| RECCORD or ROW variable)] [USING variables list]<br />
</pre><br />
<br />
<br />
<pre><br />
CREATE OR REPLACE FUNCTION fxx(a int)<br />
RETURNS float AS <br />
$$<br />
BEGIN<br />
DECLARE f float;<br />
PREPARE prep(float) from 'select $1';<br />
EXECUTE prep INTO f USING a; <br />
RETURN f ;<br />
END;<br />
$$ LANGUAGE plpgpsm;<br />
CREATE FUNCTION<br />
</pre><br />
''Syntax''<br />
<pre><br />
PREPARE name [ '(' types list ')' ] FROM expression<br />
<br />
EXECUTE name [INTO (variables list | RECORD or ROW expression)]<br />
[USING variables list]<br />
</pre><br />
<br />
<pre><br />
CREATE OR REPLACE FUNCTION fx(a int) <br />
RETURNS int AS <br />
$$ <br />
#option dump <br />
BEGIN <br />
DECLARE x, y integer; <br />
DECLARE SQLSTATE char(5); <br />
<br />
DECLARE cx CURSOR FOR prepstmt; <br />
<br />
PREPARE prepstmt(int) FROM 'SELECT a*100, b*100 FROM Foo WHERE Foo.a = $1'; <br />
<br />
OPEN cx USING a; <br />
<br />
FETCH cx INTO x, y; <br />
WHILE SQLSTATE = '00000' DO <br />
PRINT x, y; <br />
FETCH cx INTO x, y; <br />
END WHILE; <br />
<br />
CLOSE cx; <br />
RETURN a; <br />
END; <br />
$$ LANGUAGE plpgpsm; <br />
</pre><br />
<br />
<pre><br />
--dangerous, variables _tab and _var are security risk<br />
EXECUTE IMMEDIATE 'SELECT * FROM '|| _tab || ' WHERE a = ''' || _var ||''' INTO _a, _b;<br />
<br />
-- less security risk, _tab cannot contains special chars.<br />
-- only _tabs is security risk<br />
PREPARE psel(varchar) 'SELECT * FROM ' || _tab || 'WHERE a = ?';<br />
EXECUTE psel INTO _a, _b USING _var;<br />
</pre></div>
imported>Pavel