StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strike>Use <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-declarations.html" rel="nofollow noreferrer"><code>%ROWTYPE</code></a> in that case.</strike> </p> <h3>Edit - simple case</h3> <p>Tests by A.H. and DavidEG have shown this won't work. Interesting problem!<br> You could try a <strong>workaround</strong>. As long as your definition is like the example you can simply resort to</p> <pre><code>CREATE FUNCTION test(param1 comp_type) RETURNS integer AS $BODY$ DECLARE myvar comp_type; BEGIN return 1; END; $BODY$ LANGUAGE plpgsql VOLATILE; </code></pre> <p>But your real problem is probably not as simple as that?</p> <h3>Edit 2 - the real problem</h3> <p>As expected, the real problem is more complex: a <strong>polymorphic input type</strong>.<br> <strong>Workaround</strong> for that scenario was harder, but should work flawlessly:</p> <pre><code>CREATE FUNCTION test(param1 anyelement, OUT a integer, OUT myvar anyelement) RETURNS record AS $BODY$ BEGIN myvar := $1; -- myvar has now the required type. --- do stuff with myvar. myvar := NULL; -- reset if you don't want to output .. a := 1; END; $BODY$ LANGUAGE plpgsql VOLATILE; </code></pre> <p>Call:</p> <pre><code>SELECT a FROM test('("foo")'::comp_type); -- just retrieve a, ignore myvar </code></pre> <p>See full output:</p> <pre><code>SELECT * FROM test('("foo")'::comp_type); </code></pre> <h3>Note for PostgreSQL 9.0+</h3> <p>There has been a crucial update in v9.0. I quote the <a href="http://www.postgresql.org/docs/current/interactive/release-9-0.html" rel="nofollow noreferrer">release notes</a>:</p> <blockquote> <ul> <li>Allow input parameters to be assigned values within PL/pgSQL functions (Steve Prentice)</li> </ul> <p>Formerly, input parameters were treated as being declared CONST, so the function's code could not change their values. This restriction has been removed to simplify porting of functions from other DBMSes that do not impose the equivalent restriction. An input parameter now acts like a local variable initialized to the passed-in value.</p> </blockquote> <p>Ergo, in addition to my workaround, you can utilize input variables directly.</p> <h3>Dynamic Filed names</h3> <ul> <li><a href="https://stackoverflow.com/questions/25776613/how-to-clone-a-record-in-postgresql/25815243#25815243">How to clone a RECORD in PostgreSQL</a></li> <li><a href="https://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql">How to set value of composite variable field using dynamic SQL</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload