Tag Info

New answers tagged

3

You cannot use a parameter name (_t) as return type. Use polymorphic types instead: CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp) RETURNS SETOF anyelement AS $func$ BEGIN RETURN QUERY EXECUTE format(' SELECT DISTINCT ON (gid) * FROM %s WHERE vrs_start_time <= $1 ORDER BY gid, ...


2

RETURN QUERY EXECUTE was introduced with Postgres 8.4. Your version is just too old and unsupported by now. Upgrade to a more recent version. Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front. Returning anonymous records without a column ...


1

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction. Example: CREATE FUNCTION example_cursor() RETURNS refcursor AS $$ DECLARE c refcursor; BEGIN c:='mycursorname'; OPEN c FOR select * from generate_series(1,100000); return c; end; $$ language plpgsql; ...


1

Your trigger function can be improved: CREATE OR REPLACE FUNCTION trg_insup_bef_recipe() RETURNS TRIGGER AS $func$ BEGIN IF TG_TABLE_NAME = 'recipe_' THEN IF TG_OP IN ('INSERT', 'UPDATE') THEN NEW.is_baked_ := NEW.baking_instructions_ <> ''; NEW.is_roasted_ := NEW.roasting_instructions_ <> ''; END IF; RETURN NEW; ELSE ...


1

If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between: SET search_path = u111, public; SELECT * FROM foo; SET search_path = u222, public; SELECT * FROM foo; ... If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client ...


2

@a_horse already pointed out: to use x = ANY (array_value) instead of x IN (set of values). Read the manual about ANY. With long arrays (100+ elements, it depends), it is faster to unnest() and join. And it may be more convenient to use a VARIADIC function: CREATE OR REPLACE FUNCTION foo(VARIADIC intarray int[]) RETURNS void AS $func$ BEGIN UPDATE t1 ...


1

IN (...) expects a literal list of values. If you write x IN (some_array) then PostgreSQL expects x to also be an array, since you're testing to see whether the array x appears in the one-element list of arrays (some_array). If you instead want to see if the value x appears as an element of the array some_array you must write: x = ANY (some_array) (Note ...


0

If your table structures are the same, you could use table inheritance then create a master schema with all the table structures and have the others inherit table from that one: Here is an article we wrote describing the technique: http://www.postgresonline.com/journal/archives/59-How-to-Inherit,-Unherit-and-Merge-Inherit.html



Top 50 recent answers are included