Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
0answers
3 views

PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call

This is my remote function: CREATE OR REPLACE FUNCTION public._test1() RETURNS record LANGUAGE plpgsql AS $function$ DECLARE rec record; BEGIN select 1,2 into rec; return rec; END $function$; ...
1
vote
0answers
42 views

How do I parse Address into Street Number, Street Name, Type, Unit Number?

How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this? 2280 MEYERS AVE ALDERGROVE AVE & S ...
0
votes
0answers
7 views

Pass 'interval' value to date_trunc function in PostgreSQL procedure

I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns. The syntax in the ...
0
votes
1answer
23 views

What's the most appropriate type of index to use for this function-based query?

I'm using Postgres 9.5. I'm going to be running a lot of this kind of query SELECT * FROM mydb_article where regexp_replace(url, '\?.*$', '') = :url_wo_query_string so I figured I would need to ...
0
votes
1answer
48 views

Loop Block with execution doesn't persist

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist. Like this... do $$ declare dados Record; begin for ...
0
votes
0answers
18 views

Returning a set datatype from MariaDB function

Given below is the syntax for the MariaDB's CREATE FUNCTION: CREATE [OR REPLACE] [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }] [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([...
0
votes
0answers
13 views

PostgreSQL Function not returning message in Python 2.7 Script using EXCEPT NOTICE

I was not able to make the PostgreSQL function return the raised message in the python script it was being called from. The scenario I wanted to make was:- 1) If the first table was created, a ...
0
votes
0answers
35 views

PostgreSQL Function: Returning Boolean Values depending on whether a table exists

This is my rookie attempt to create a function where it would return True or False depending on whether a table exists in the db. Unfortunately it outputs an empty table, and no raised messages; ...
0
votes
1answer
34 views

Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows. In the first database restored, some functions are with compilation errors. The query below informs you that ...
1
vote
0answers
16 views

Filtered Merge Statement [duplicate]

This is, probably, a really simple answer, but I am not coming up with it... Any help would be greatly appreciated: I created a web form, to a stored procedure, that submits userID, deptID, and a ...
-1
votes
1answer
36 views

How to Set the Transaction Isolation level in a Table Value Multi Function

How do I set the transaction isolation level within in a table value multi function? I know it can be done in a stored procedure. create function dbo.Test() RETURNS @TestTable Table (test varchar(...
0
votes
1answer
29 views

How to trigger a function with DDL query?

I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored ...
0
votes
0answers
34 views

Postgres - Ignore result of function call in select statement

I would like a way to run a function on all values returned from a query where the return value of the function is unimportant (even void in this case). A select statement may be the wrong way to do ...
2
votes
1answer
65 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
36 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
113 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
78 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
29 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
61 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
64 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
50 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
61 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
9 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
52 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
111 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
30 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 $...
5
votes
1answer
102 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
46 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
251 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
57 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
58 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
84 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
121 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
43 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
111 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
175 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 ...
3
votes
1answer
93 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
417 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
101 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
89 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
37 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
64 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
844 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 ...
3
votes
1answer
284 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
23 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
142 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
134 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
798 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 ...