This would be shorter, faster and safer: CREATE OR REPLACE FUNCTION get_parent_ltree(parent_id bigint, tbl_name regclass , OUT parent_ltree ltree) LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘SELECT l_tree FROM %s WHERE id = $1′, tbl_name) INTO parent_ltree USING parent_id; END $func$; Why? Most importantly, use the USING clause of EXECUTE for parameter values. Don’t convert them … Read more
As @Pavel explained, it is not simply possible to traverse a record, like you could traverse an array. But there are several ways around it – depending on your exact requirements. Ultimately, since you want to return all values in the same column, you need to cast them to the same type – text is … Read more
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
From the fine manual: 36.1. Overview of Trigger Behavior […] For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers. Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement. And from … 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
Do it as plain SQL CREATE OR REPLACE FUNCTION tst_dates_func() RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$ select a.date_value, a.date_id, a.date_desc from dates_tbl a; $BODY$ LANGUAGE sql; If you really need plpgsql use return query CREATE OR REPLACE FUNCTION tst_dates_func() RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$ BEGIN perform … Read more
DO command vs. PL/pgSQL function A DO command does not return rows. You can send NOTICES or RAISE other messages (using default LANGUAGE plpgsql), or you can write to a (temporary) table and later SELECT from it to get around this. But really, create a function instead, where you can define a return type with … Read more
Just keep the first 32 bits or 64 bits of the MD5 hash. Of course, it voids the main property of md5 (=the probability of collision being infinitesimal) but you’ll still get a wide dispersion of values which presumably is good enough for your problem. SQL functions derived from the other answers: For bigint: create … Read more
There are no “table variables” in plpgsql. That’s something you would find in SQL Server. Use a temporary table instead: BEGIN CREATE TEMP TABLE table_holder AS SELECT * FROM table_holder WHERE <some condition> ORDER BY <some expression> ; … END A temporary table exists for the lifetime of a session. To drop it at the … Read more
4294967295 must be used as the bitmask to select 32 bits (instead of 4294967296). That’s the reason why currently you get the same value for different inputs. I’d also suggest using bigint for the types of l2 and r2, they shouldn’t really differ from r1 and l1 And, for better randomness, use a much higher … Read more