Tagged Questions
4
votes
3answers
592 views
Refactor a PL/pgSQL function to return the output of various SELECT queries
UPDATED for better understanding
I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT ...
5
votes
4answers
3k views
Table name as a PostgreSQL function parameter
I want to pass a table name as a parameter in a Postgres function. I tried this code:
CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer
AS $$
BEGIN
IF EXISTS (select ...
13
votes
3answers
858 views
The forgotten assignment operator “=” and the commonplace “:=”
The documentation for PL/pgSQL says, that declaration and assignment to variables is done with :=.
But a simple, shorter and more modern (see footnote) = seems to work as expected:
CREATE OR ...
8
votes
1answer
3k views
insert with dynamic table name in trigger function
I'm not sure how to achieve something like the following:
CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$
DECLARE
shadowname varchar := TG_TABLE_NAME || 'shadow';
...
24
votes
4answers
33k views
PostgreSQL return setof record ( virtual table )
i need a postgres function to return a virtual table ( like in oracle ) with custom content. The table would have 3 columns and an unknown amounts of rows..
i just couldn't find the correct syntax on ...
4
votes
3answers
1k views
PostgreSQL parameterized Order By / Limit in table function
I have a sql function that does a simple sql select statement:
CREATE OR REPLACE FUNCTION getStuff(param character varying)
RETURNS SETOF stuff AS
$BODY$
select *
from stuff
where col = ...
4
votes
4answers
2k views
How to set value of composite variable field using dynamic SQL
Given this type:
-- Just for testing purposes:
CREATE TYPE testType as (name text)
I can get the value of a field dynamically with this function:
CREATE OR REPLACE FUNCTION get_field(object ...
1
vote
1answer
266 views
Merge a table and a change log into a view in PostgreSQL
My PostgreSQL database contains a table that stores instances of a registered entity. This table is populated via spreadsheet upload. A web interface allows an operator to modify the information ...
10
votes
4answers
11k views
Return multiple fields as a record in PostgreSQL with PL/pgSQL
I am writing a SP, using PL/pgSQL.
I want to return a record, comprised of fields from several different tables. Could look something like this:
CREATE OR REPLACE FUNCTION get_object_fields(name ...
3
votes
4answers
8k views
PostgreSQL function returning multiple result sets
Is it possible to return multiple result sets from a Postgres function, like in MSSQL:
`
CREATE PROCEDURE test
AS
SELECT * FROM first_table
SELECT * FROM second_table
`
4
votes
1answer
2k views
Dynamically-generated table-name in PostgreSQL COPY command
This PostgreSQL COPY command works:
copy tablename from E'c:\\abc\\a.txt';
but I want the tablename to be dynamically generated. How can I do this?
4
votes
4answers
5k views
Tool for translation of Oracle PL/SQL into Postgresql PL/pgSQL
It there a tool (preferably free) which will translate Oracle's PL/SQL stored procedure language into Postgresql's PL/pgSQL stored procedure language?
3
votes
2answers
4k views
PL/pgSQL checking if a row exists - SELECT INTO boolean
I'm writing a function in PL/pgSQL, and I'm looking for the simplest way to check if a row exists.
Right now I'm SELECTing an integer into a boolean, which doesn't really work. I'm not experienced ...
2
votes
2answers
292 views
Correctly inserting literals in PL/PgSQL EXECUTE dynamic queries
The following is part of a plpgsql function. The problem is that the result of source_geom and target_geom is a character varying data type, and therefore I need to surround the both source_geom and ...
2
votes
2answers
2k views
PHP array to postgres array
Now php can't work directly wit Postgresql array. For example, php taking postgresql array like
'{"foo","bar"}'
I need simple php function to create multidimensional postgresql array from php ...
