23

The results of a multi-statement table-valued function (msTVF) are never cached or reused across statements (or connections), but there are a couple of ways that an msTVF result may be reused within the same statement. To that extent, an msTVF is not necessarily repopulated each time it is called. Example msTVF This (deliberately inefficient) msTVF returns ...


13

Let's start by comparing execution plans: tinker=> EXPLAIN ANALYZE SELECT * FROM generate_series(1,1e7); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------...


12

Your numbers table is a heap and is potentially being fully scanned each time. Add a clustered primary key on Number and try the following with a forceseek hint to get the desired seek. As far as I can tell this hint is needed as SQL Server just estimates that 27% of the table will match the predicate (30% for the <= and reduced down to 27% by the <&...


11

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details: SELECT 'Inspections'::text AS data_label , count(i.reporting_id) AS daily_count , d.day AS date_column FROM ( SELECT generate_series(timestamp '2013-01-01' , ...


11

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


11

1. Subquery expression You can fix it with parentheses like @a_horse commented: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1)); But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system ...


9

If you must use a single query (as required by a single inline function), you can use one of the two options below (illustrated in my recent answer to Relating 2 tables with possible wildcards?): Option 1 Use multiple APPLY clauses with a startup condition for each using an outer reference from a previous apply in the chain. The efficiency of this method ...


9

You can use UNNEST. select unnest(ports) as port, count(*) from foo group by port; Using more than one UNNEST in the same query (or the same select list, anyway) is confusing and is probably best avoided.


8

Table Variables, whether created from a DECLARE or from a RETURNS in a CREATE FUNCTION statement, as well as User-Defined Table Types (UDTTs), do not allow for constraints to be named. According to the MSDN page for CREATE FUNCTION: Arguments ...         < column_constraint >::= and < table_constraint>::=     &...


7

The error message isn't very helpful: regress=> SELECT * FROM compute_all_pair_by_craig(100); ERROR: a column definition list is required for functions returning "record" LINE 1: SELECT * FROM compute_all_pair_by_craig(100); but if you rephrase the query to call it as a proper set-returning function you'll see the real problem: regress=> SELECT * ...


6

In other RDBMS (like SQL Server before 2008 - as per Paul's comment) one might cross join to a subquery with UNION ALL SELECT, but there are more convenient and efficient options in Postgres. And you don't need a CTE for this. You can use it, but it has no performance benefit. Provide a set with VALUES: VALUES computes a row value or set of row values ...


6

Functions cannot return data to a client so I can't think of any use of SET NOCOUNT ON. If you do try to add it a function you'll get an error. SQL Server considers it to cause a side effect: function_body Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the ...


6

You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help: DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF')); DECLARE @name SYSNAME; OPEN C; FETCH NEXT FROM C INTO @name; WHILE @@FETCH_STATUS=0 BEGIN EXEC sp_recompile @name; FETCH NEXT FROM C ...


5

As I understand, the function is called and runs at the cross apply, meaning that it should be calculating a value even if it is not in the select, so why would it be faster not to include it in the select? The optimizer is very good at removing subtrees that compute expressions that are not needed in the final result (top-level projection). When you remove ...


4

There are a few ways of doing this in PostgreSQL depending on the versions you have to support. The simplest is: create or replace function plpgSqlProc () returns setof integer as $$ BEGIN RETURN QUERY select * from foo; END; $$ LANGUAGE PLPGSQL; This being said, I find where you don't need helper procedural code, plain SQL functions are usually better. ...


4

You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution. With a table like this: CREATE TABLE things_to_do AS SELECT * FROM generate_series (1,6) AS thing_id; A simple solution for the given example SELECT * FROM things_to_do JOIN ( -- derived table with prepared timestamps SELECT row_number() OVER ()...


4

The estimate for the inner (lower) input to a nested loops join is per iteration in SSMS. The 'actual' number of rows shown in SSMS is a total over all iterations. This often causes confusion, and is the result of a questionable design decision. The one row estimate therefore needs to be multiplied by the estimated number of executions. Many people prefer ...


4

I don't know sql-server well enough to state whether it works this way or not, but in theory there is no way you can say that one part of the union is evaluated before another. I.e. even though you have a perfect match, you may still end up with an approximate one. You can however force this behavior by adding a priority to each part of the union and order ...


4

The best solution depends on the exact definition of your setup. For the example setup it's trivial: Serial integer columns without gaps. SELECT DISTINCT 1 + trunc(random() * 100)::int AS a_id , 10001 + trunc(random() * 100)::int AS b_id FROM generate_series(1, 1100) g -- enough excess to make up for possible dupes LIMIT 1000; -- only ...


4

I am afraid that's only possible with unnest() after all. unnest() with multiple parameters is a special Postgres feature, that's internally rewritten into multiple unnest() calls that are combined more reasonably than parallel unnest() in the SELECT list would be, i.e. in a sane way if the number of elements does not match. The behavior was finally ...


4

Yes, this is expected. Functions and views that use SELECT * end up storing part of the metadata themselves relating to the underlying columns, so it is very easy for them to get confused. I talk about this problem in the following post from 2009: Bad habits to kick : using SELECT * / omitting the column list And also in my recent GroupBy presentation: T-...


4

If Tzid and DT_WhenSwitch define a unique row I recommend clustering the dbo.DSTLookup table by those two columns. You can make those columns the primary key if you want or you can just make them be the clustered index. CREATE TABLE dbo.DSTLookup ( [Id] int, [Tzid] int, [DT_WhenSwitch] datetime, [DSTOffSetSeconds] int, [...


4

The error message is telling you that you can't use a set returning in the SELECT list. You need to put it into the FROM clause: SELECT array_agg(t.k) FROM table tbl, jsonb_object_keys(tbl.data) as t(k) GROUP BY tbl.some_id WHERE tbl.some_id = 3;


4

Set returning functions should be put into the from clause. Putting them into the SELECT clause is allowed, but can result in strange result (as you have noticed). The clean approach would be: select a.vin, r.data ->'attributes'->>'name' as style from vins a left join describe_vehicle b on a.vin = b.vin left join jsonb_array_elements(b....


3

Yes - if you use functions in a language other than SQL, or if you define them as STRICT. Essentially, you must prevent inlining of the function. If the function isn't inlined, then predicates can't be pushed down through it and it can't be flattened. Only SQL functions are eligible for inlining, and only if they are not defined as STRICT.


3

Combine all queries using LEFT OUTER JOINS and use the resulting columns in reverse order as parameters of COALESCE function. This function will evaluate all the parameters from left to right and take the first not null value.


3

You cannot include a table declaration in the stored procedure declaration. If you really want to pass rows of data, create a table-valued parameter type, then use it in your stored procedure. How about a simpler table valued function, that calculates product and quotient of 2 integers: CREATE FUNCTION fn_CalcProductQuotient( @Value1 INT, @Value2 INT) ...


3

It's cleaner to use set returning functions in the FROM clause where possible. The SQL standard does not allow them in the SELECT list. And it's almost always possible since we have LATERAL joins. SELECT port, count(*) AS ct FROM tbl t, unnest(t.ports) AS port -- implicit LATERAL join GROUP BY port; What is the difference between LATERAL and a subquery ...


3

You can use a cursor. create type my_type as (id int, other_column text); create or replace function get_data () returns setof my_type language plpgsql as $$ declare cur cursor for select id, other_column from my_table limit 20000; rec record; begin for rec in cur loop update other_table set status = 2 where id =...


3

AIUI, your wish is to shorten the RETURNS clause of the function. Not sure if you want to establish a dependency on the row type of the table at the same time, but that would make sense here, too. The form RETURNS SETOFrettype relies on the used type to be stored in the system catalogs. The manual: The return type can be a base, composite, or domain type,...


Only top voted, non community-wiki answers of a minimum length are eligible