Questions tagged [functions]
A function performs some specified work, usually taking parameters as input. In terms of databases these may be specific to SQL or to the database vendor.
2
votes
1answer
61 views
How to find function definition
How can I find the definitions of below system functions in SQL Server:
has_access
permission_name
I tried with DAC connection but getting below error:
Msg 15009, Level 16, State 1, Procedure ...
0
votes
1answer
31 views
PostgreSQL functions inside where clause
I have table like this:
table a (
id
--
p1 text,
p2 text,
p3 text
)
When I write some function, which use this table, I need to write code like this
create or replace function ...
...
1
vote
1answer
32 views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
4
votes
1answer
67 views
SQL Function, get value to send to CLR
I have a CLR to use the powershell to have access to the RichTextBox Object from .net.
On the SQL Function I have created like this:
CREATE FUNCTION [dbo].[FicheiroChines](@rtf [nvarchar](max), @...
2
votes
1answer
26 views
Postgres triggers for group_id
I have a table that looks like this:
CREATE TABLE transactions(
id SERIAL NOT NULL PRIMARY KEY,
value NUMERIC(10,3),
note TEXT,
group_id INTEGER);
I'd like to update the group ID automatically with ...
2
votes
3answers
48 views
Pass array of mixed type into stored FUNCTION
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.
the problem here is, that one argument is list of n ...
0
votes
2answers
43 views
Postgres list constraints that use a stored procedure
I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.
something like:
CREATE TABLE payment (
amount bigint,
CONSTRAINT amount_gt_0 CHECK (...
0
votes
2answers
48 views
How to efficiently handle querying for different combinations of optional parameters
Consider the following Tables:
Table: Parts
part_id INT PK
part_number VARCHAR
Table: Jobs
job_id INT PK
job_number VARCHAR
Table: Job_Parts
part_id INT FK PK
job_id INT FK ...
0
votes
1answer
45 views
SQLServer Row level security - Enforce from within database
Have a multi-tenant app running on a single sql 2016 db, I have a RLS (row level security ) predicate on customer_id (which is unique for each tenant) and the column exists in all tables.
CREATE ...
1
vote
0answers
8 views
Querying a synonym (for a table using linked server) gives no data in return
I have a function which run in an application at a server. This server have a ODBC connection (user DSN) to the SQL server which is set to use windows authentication.
The function is supposed to ...
0
votes
1answer
38 views
Custom return type of a function in Oracle PL SQl
I have a query and I want to wrap it into a function. Currently query returns in_count, in_sum, out_count, out_sum. I only know how to return a "primitive type". So currently I have four different ...
1
vote
1answer
46 views
How to use variables in procedure (not function)? [closed]
As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:
declare a variable
select a value into a variable
insert the variable into ...
1
vote
1answer
24 views
Return table with separate columns from function
I have this function, it's simple enough:
CREATE OR REPLACE FUNCTION test_function()
RETURNS TABLE(
id INTEGER,
organization_id INTEGER,
email_address CHARACTER VARYING
)
AS $...
4
votes
1answer
82 views
PostgreSQL UDF (User Defined Functions) overhead
Disclaimer
The task may seem esoteric, but nevertheless I want to create a POC of some sorts.
The goal
My goal is to make PostgreSQL database (version 10) expose an API to an application that uses ...
0
votes
1answer
38 views
Realtime Update MySQL on every Update occurred on PostgreSQL
I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
1
vote
1answer
87 views
What is included in ALL permissions for functions in PostgreSQL?
I am trying to find references to what the difference between EXECUTE and ALL is in:
GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION | ALL FUNCTIONS IN SCHEMA }...
but all I can find is what ...
1
vote
2answers
56 views
Functions causing Serial plan.What to use instead? [closed]
I have few stored procedures in my SQL Server database.I see that these stored procedures are running in serial mode from their execution plan.
For example :
CREATE PROCEDURE [dbo].[DemoProc]
@...
3
votes
1answer
45 views
What is the difference between a SQL function doing a SELECT versus a PLPGSQL function doing a RETURN QUERY SELECT?
Is there a difference between a plain SQL function doing a SQL query:
create function get_sports() returns setof sport as
$body$
select * from sport;
$body$
language sql stable;
and PLPGSQL ...
4
votes
1answer
82 views
TSQL - create a function that takes a multi-row query result as an argument
I work with a variety of servers on mostly SQL Server 2014 and earlier.
I have boiler-plate code that can take the results of an arbitrary select statement (returning a list of varchar or the like) ...
0
votes
1answer
54 views
Equivalent of DB2 function “decimal(numeric expression, precision, scale)” in PostgreSQL
Is there an equivalent of the DB2 function decimal(numeric expression, precision, scale) in PostgreSQL? Or how would I do this functionality in PostgreSQL?
The DECIMAL function returns a decimal ...
1
vote
0answers
23 views
PostgreSQL- user-defined types: note sure what internallength/alignment is for linked list type
I'm currently trying to implement at user-defined data type in Postgres in the form of a linked list with each node holding an int and a pointer to the next node, here are my input functions:
// ...
3
votes
0answers
42 views
Need a Function that allows parameters to pass as argument for logic inside the function in Greenplum
Suppose I have a query :-
select 'public.test123 is' || ' ' || quote_literal(obj_description('public.test123'::regclass)) || ';' as test;
test
-------------------------------------
...
2
votes
3answers
135 views
Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change?
I was working on a query at work, that had a left join like
cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x
The actual execution plan for this query was fairly close, est ...
2
votes
1answer
88 views
Efficiency of Scalar UDF vs TVF
I am trying to optimize the rollup code for my company and ran into a very peculiar issue. I converted many Scalar functions to be TVFs and they all seem to run more quickly than the original, which ...
1
vote
1answer
88 views
How to create `instead of` trigger on a table in postgres?
I want to create a trigger for a table in postgresql. My table contains data about events and includes room number, start time and event duration. Upon new insertion to the table I want to check if ...
0
votes
0answers
29 views
Postgresql function slow down drastically after 2nd query
Issue: After starting a psql session, I execute the following query 3 times, the first 2 times, it runs quickly (651ms); the 3rd time (and after), it slows down drastically (96,176ms). I cannot ...
3
votes
1answer
82 views
How can I find out if a SQL Function can be inlined?
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
8
votes
1answer
360 views
Can declaring function volatility IMMUTABLE harm performance?
Postgres functions are declared with volatility classification VOLATILE, STABLE or IMMUTABLE. The project is known to be very strict with these labels for built-in functions. And with good reason. ...
1
vote
1answer
82 views
MySQL / PostgreSQL: how to call a script when a row inserted / updated
Let's assume, I have two DBs, either MySQL or PostgreSQL (sorry, but we have not yet decided what DBMS to use).
Every second multiple rows are being inserted or updated on the first DB. For each ...
2
votes
1answer
74 views
Function in JOIN condition
I have big dynamically constructed query with
FROM main_table t
LEFT JOIN table1 t1 ON my_func(t.col1, 1) = t1.col1
...
LEFT JOIN table6 t6 ON my_func(t.col1, 6) = t6.col1
Query is very slow, but if ...
-2
votes
1answer
36 views
What is TRANSFORM FOR TYPE?
PostgreSQL 11 introduces TRANSFORM FOR TYPE, from these docs by HP, PostgreSQL 11 New_Features
postgres=# CREATE EXTENSION jsonb_plperl CASCADE ;
NOTICE: installing required extension "plperl"
CREATE ...
1
vote
1answer
58 views
Logic of Assigning values to variables in Scalar-valued Functions
I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.
While troubleshooting my rewrites I came across some ...
8
votes
1answer
710 views
Is it Possible to Overload SQL Server Functions?
Is it possible to overload a sql server function? Either a scalar, like ltrim, or an aggregate function, like count?
Even if this was a really, really, bad idea. Is it possible?
Somewhat of a ...
0
votes
0answers
32 views
phpmyadmin keeps telling me that NEWID() doesn't exist as a function?
I was trying to use the newID() function to generate a newid(). I was trying to use this query
`UPDATE `user_data` SET `uniqueID` = newid() WHERE `shortid` = 1`
I tried writing with newid() in ...
3
votes
1answer
156 views
How could a “SECURITY DEFINER” function in PG be insecure with an improper search_path?
The PG docs provides this example of a properly written security definer function with a search_path:
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed ...
0
votes
0answers
21 views
Task oriented VS general function definition
Which approach is better to use for an application with more than 1 thousand db functions:
1- Defining Global functions and filtering data in server side and using data
( It causes some performance ...
0
votes
2answers
84 views
How to get around not being able to use Temp Tables in Functions?
So, to get straight to the point. A Client wants to be able to view all of their staff of a certain position are going to be available to perform a set of shifts between 2 dates.
Seems simple, have a ...
3
votes
1answer
97 views
Is there any performance advantage to marking a SQL function as LEAKPROOF?
PostgreSQL has an attribute LEAKRPOOF which you can use to declare a function,
LEAKPROOF indicates that the function has no side effects. It reveals no information about its arguments other than by ...
1
vote
2answers
558 views
Postgres - Return default value if regex match fails
I'd like to attempt a regex match, and return null if it fails.
The following query attempts to find the first number in a string. The result ignores the entry with text 'blah'. I'd prefer it to ...
3
votes
2answers
96 views
Using PostgreSQL functions instead of joining?
I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum.
I have a function:
CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$
...
10
votes
3answers
1k views
Fastest way to count how many dates' ranges cover each date from series
I have a table (in PostgreSQL 9.4) that looks like this:
CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES
(1, '2018-01-01', '2018-01-31'),
...
0
votes
2answers
36 views
Is it possible to have both a subquery and a function in a WITH clause?
In Oracle 12c, we can include subqueries in the WITH clause:
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc....
0
votes
1answer
95 views
How to get newly inserted row ids when inserting with execute?
I have a function which returns newly inserted row count:
BEGIN
EXECUTE (
SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
_tbl, ...
0
votes
0answers
40 views
In-line TVF is using indexed views in a UNION - parameter should eliminate most of them, except it doesn't
I am using SQL Server 2016-SP1-CU8
Here is the code to setup my scenario:
--1. Drop indexed views (in case of re-running
IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1
IF ...
2
votes
0answers
20 views
Does specifying STRICT on a PostgreSQL trigger procedure do anything?
Does specifying CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT or STRICT (https://www.postgresql.org/docs/current/static/sql-createfunction.html) on a trigger procedure in PostgreSQL do anything?
...
0
votes
0answers
43 views
Alternative to Mutli-line Table Valued Functions
I currently have a relatively large MTVF returning a set of data, the code I have provided below is a small subsection of what I have in place. All but 1 part of my function is built from a Cte chain ...
-1
votes
2answers
141 views
Can I create a faster version of unnest() over int[]?
I have a column of int[] type. now I want to convert int[] value into rows without using unnest() function. I want to create my own function which takes int[] as input and convert it into rows.
How ...
0
votes
2answers
2k views
How to create a table dynamically based on json data?
I have a json that I get as a file, I have loaded that in the Postgres database.
Here is the json data:
{
"tablename": "test",
"columns": [
{
"name": "field1",
"datatype": "BigInt"...
2
votes
3answers
120 views
Why does my scalar UDF perform so differently on two different (but extremely similar) servers?
Recently, I was troubleshooting a strange performance issue that impacted an application's production environment, but not any of the lower environments. I managed to replicate the issue in its ...
8
votes
4answers
1k views
Most efficient way to call same Table-Valued Function on multiple columns in a Query
I am trying to tune a query where the same table-valued function (TVF) is called on 20 columns.
The first thing I did was convert the scalar function into an inline table-valued function.
Is using ...
