RETURN NEXT just returns what output parameters currently hold. The manual: If you declared the function with output parameters, write just RETURN NEXT with no expression. You object: There are no OUT parameters. Output parameters are declared among function parameters with the keyword OUT or INOUT, or implicitly in your RETURNS clause: RETURNS TABLE(column1 integer, … Read more
You can work around this by piping the file through a program. For example I just used this to copy from a 24GB file on Windows 10 and PostgreSQL 11. copy t(c,d) from program ‘cmd /c “type x:\path\to\file.txt”‘ with (format text); This copies the text file file.txt into the table t, columns c and d. … Read more
Rows are returned in an unspecified order, per sql specs, unless you add an order by clause. In Postgres, that means you’ll get rows in, basically, the order that live rows read on the disk. If you want a consistent order without needing to add an order by clause, create a view as suggested in … Read more
That is expected and documented behaviour: From the manual: Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within … Read more
You can actually just straight up change the column order, but I’d hardly recommend it, and you should be very careful if you decide to do it. eg. # CREATE TABLE test (a int, b int, c int); # INSERT INTO test VALUES (1,2,3); # SELECT * FROM test; a | b | c —+—+— … Read more
I can’t remember the last time I actually needed to use an explicit cursor for looping in PL/pgSQL. Use the implicit cursor of a FOR loop, that’s much cleaner: DO $$ DECLARE rec record; nbrow bigint; BEGIN FOR rec IN SELECT * FROM pg_tables WHERE tablename NOT LIKE ‘pg\_%’ ORDER BY tablename LOOP EXECUTE ‘SELECT … Read more
OS X 10.8 comes with pg_dump version 9.1.4 in the /usr/bin directory, along with psql and other programs that are client-side PostgreSQL tools. It does not mean that PostgreSQL as a server is installed (unless you have OS X Server Edition). So you don’t have to uninstall PostgreSQL because it’s not installed and it’s better … Read more
To calculate the median in PostgreSQL, simply take the 50% percentile (no need to add extra functions or anything): SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY x) FROM t;
This is not possible with a simple DEFAULT value, as the manual clearly states: The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). You could use a trigger instead: CREATE OR REPLACE FUNCTION trg_foo_b_default() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN — For just a … Read more
The stored procedure won’t just return the result of the last SELECT. You need to actually return the value: CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$ BEGIN — …. RETURN(SELECT dblink_disconnect()); END $$ LANGUAGE plpgsql; You’re getting the error because Postgres expects the function to return something of type text, but your function … Read more