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
1answer
31 views

PostgreSQL 11 error: column p.proisagg does not exist

Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error: ERROR: column p.proisagg does not exist LINE 18: ...
0
votes
0answers
43 views

Cannot get a user function to co-operate with a subquery in mariadb

The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing. Notes: Problem ...
0
votes
1answer
18 views

mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

I do not understand why MySQL is not using the index for the below query. I have a table named users and its create table is this table contains data. Now I am running SQL select statements and it'...
1
vote
1answer
41 views

Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?

I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists). An example ...
10
votes
1answer
205 views

function to receive a character input and return date format (with incorrect input)

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect ...
9
votes
1answer
195 views

Apparently, my CLR assembly function is causing deadlocks?

Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL ...
0
votes
1answer
16 views

Postgresql table function: get where condition

I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data. Here, there is a very simple example: create or replace function public....
0
votes
1answer
56 views

Trigger for multiplying two columns from different tables with a functions in mysql

I have been endelessly trying to get a trigger to work, with many different variations and all ending in errors. I have finally just decided to ask for help, since im still a beginner at this. So ...
3
votes
1answer
46 views

PostgreSQL 11: Replace function body by users from the same group

In the documentation of CREATE FUNCTION is stated that ''You must own the function to replace it (this includes being a member of the owning role).'' Two questions: I don't understand what exactly ...
0
votes
2answers
25 views

Default values in function, arguments dependency, PostgreSQL

I would like to write a function with two arguments, which would have dependency. Something like this: CREATE OR REPLACE FUNCTION years_compare( IN year1 integer DEFAULT date_part('year'::...
0
votes
0answers
21 views

Is it possible to execute a postgresql function when any view is refreshed?

I want to alert my application that are utilizing views when the view is refreshed. Is this possible using PostgreSQL built-in functionality? If not are there any other approaches to tackle this?
1
vote
1answer
211 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
54 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
1answer
28 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
25 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
53 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
25 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
14 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
115 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
81 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
94 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
39 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
43 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
68 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
40 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
261 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
90 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
34 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
119 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
73 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
52 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
76 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
10 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
73 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
431 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
31 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
147 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
53 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
585 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
60 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
76 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
85 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
265 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
26 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
137 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
132 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
294 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
105 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 ...