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 ...
