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
2answers
68 views
Can SQL Server 2008 / 2012 have execute permission in a function for a user, but not for a table used in the function?
I have a function that returns a table. This function SELECTS DB1.dbo.table1
user1 has SELECT permission in this function only, but when selecting from the function error comes up stating that the ...
1
vote
1answer
39 views
Pass a table array as a parameter to an upsert function in postgresql
i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
1
vote
0answers
41 views
Calling a function in remote database inside a stored procedure
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I ...
3
votes
1answer
59 views
How does this undocumented syntax work? {fn CurDate()} or {fn Now()} etc
Recently I've been looking through some fairly old stored procedures that were written for SQL Server 2005, and I've noticed something that I don't understand. It appears to be some type of function ...
0
votes
1answer
16 views
Cannot create perlplu function
Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos.
From the shell, I have executed:
createlang plperl db_name;
createlang plperlu db_name;
As the superuser running psql, I have ...
2
votes
1answer
49 views
How to access the number of rows accumulated by RETURNs in PL/pgSQL
When performing
RETURN QUERY ...
in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ...
1
vote
2answers
76 views
Recursive query using plpgsql
I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.
I have a data table and a table to link the data together:
DATATABLE
-----------
id ...
4
votes
1answer
565 views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
5
votes
3answers
106 views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
-1
votes
1answer
81 views
Undefined reference to my function [closed]
I'm working on a project on postgresql 8.4 server side. I'm adding some functionality in order to store some query informations.
I need to call two functions (start_create_profile() and check()) ...
0
votes
1answer
74 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
6
votes
3answers
180 views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
1
vote
1answer
717 views
Invalid use of side-effecting operator 'INSERT EXEC' within a function [closed]
I'm trying to create a scalar function that will return a delimited list from some sql that would be passed in. Unfortuantly where I work the previous people responsible for the database thought it ...
4
votes
1answer
318 views
Oracle extract slow function call from a WHERE clause
I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this:
CREATE TABLE MY_TABLE
(
SOMETHING NUMBER(15,1) NOT NULL,
...
0
votes
2answers
69 views
looping through a table type variable
I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value.
CREATE OR REPLACE FUNCTION ...
