Questions tagged [user-defined-type]

User-Defined Types (UDTs) are custom datatypes in Microsoft SQL Server 2005 or newer. UDTs are one of the five SQLCLR object types and rely on the .NET Framework. They are most commonly written in C# or VB.NET.

Filter by
Sorted by
Tagged with
-1
votes
1answer
80 views

Unable to declare user-defined variable in MySQL

I'm creating procedure called min_max to find minimum and maximum for every column in a table. But I'm encountering an error for declaring the variable. See below: DELIMITER // DROP PROCEDURE IF ...
1
vote
1answer
118 views

How to insert nested UDT types

I have all these case classes :- case class Role(id:UUID, roleName:String, description:Option[String], isVisible:Boolean, isDefault:...
0
votes
1answer
80 views

Are user defined types (UDTs) available in an Azure SQL database managed instance?

Is it possible to create user defined types and user defined table types in an Azure SQL database managed instance?
2
votes
1answer
131 views

Can't use CAST, Convert with UDT in Sql Server 2014 [closed]

My tables consist of many decimal columns with different precision. I wanted to standardize the usage of precision by creating several UDTs with predefined precision and use them as column type ...
3
votes
3answers
351 views

How do I create a bounded range type in the SQL Server?

I want to create a bible verse table that has one field representing a range of bible verses. Currently I represent this range as pair of values (VerseBegId and VerseEndId). I want something like ...
1
vote
0answers
41 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: // ...
6
votes
2answers
576 views

Determine if user-defined type is ENUM

Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM? Essentially we have the following: CREATE TYPE foo AS ENUM ( 'Sometimes', 'You', 'Wanna', 'Go', '...
0
votes
1answer
24 views

Noob question about user accounts

I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with. So I'm creating a webapp that is essentially an online art gallery store. The database will contain ...
1
vote
1answer
170 views

Need an efficient routine to pass object name, with or without “[” and “]” brackets, to get ObjectID

Someone in their wisdom created a table called user: select schema_name(schema_id) ,* from sys.tables where type = 'U' and name like '%user%' select top 100 * from usr.user Msg 156, Level 15, ...
3
votes
1answer
250 views

UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly. Step by step: Assembly: CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH ...
0
votes
1answer
864 views

Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

I have a function which returns: TABLE (id TEXT, collected TIMESTAMPTZ) In another function, I would like to be able to pass the result of that first function as a parameter. For example: CREATE ...
3
votes
1answer
2k views

Supertypes and subtypes

I am trying to design a database that has supertype/subtypes. I have the following users: - basic user - business user - administrator (super user). All three users share a common table (users) ...
2
votes
2answers
184 views

SQLCLR User-Defined Type (UDT) SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# SQLCLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
0
votes
0answers
2k views

PostgreSQL and array of custom type

One of the table I have looks like this CREATE TABLE orders_suppliers_contacts ( supplier_id bigint NOT NULL, name character varying(64) NOT NULL, phone character varying(18), notes character ...
0
votes
0answers
482 views

Proper way to handle NULL (empty) values for user-defined table types passed as params to stored procedure

My stored procedures accepts table value parameters for querying data. Usually those are custom user-defined table types for where clause. Business logic of stored procedures push me to these @...
5
votes
1answer
7k views

How to list the permissions on User-Defined Table Types?

I am using some user-defined table types. They are very useful. you can see information about them by running this simple script: SELECT o.* from sys.table_types o I have a procedure that shows me ...
9
votes
1answer
7k views

Multiple User Types - DB Design Advice

I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. ...
6
votes
1answer
18k views

Operand type clash: my first User Defined Type with list of Integers, how to test via SSMS

I found this wonderful code here CREATE TYPE [dbo].[StringList] AS TABLE( [Item] [NVARCHAR](MAX) NULL ); GO CREATE PROCEDURE [dbo].[sp_UseStringList] @list StringList ...
0
votes
1answer
196 views

User defined data types causing implicit conversions

user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into ...
3
votes
2answers
2k views

User defined table type with a check constraint

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on ...
8
votes
1answer
974 views

Understanding MS SQL Server Date Types

Consider the following: declare @dt datetime, @dt2 datetime2, @d date set @dt = '2013-01-01' set @dt2 = '2013-01-01' set @d = '2013-01-01' select convert(varbinary, @dt) as dt, convert(...
3
votes
1answer
418 views

PostgresSQL: Get single attribute of UDT in SELECT statement

I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work. Creation of type ...
13
votes
4answers
25k views

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't ...
1
vote
2answers
186 views

Postgresql: type_modifier in UDT

In the manual for creating UDTs in postgresql I found the following statement: PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this ...
8
votes
2answers
6k views

MySQL Permissions Necessary To Reset Password

On my team there is a desire for some users to periodically update their MySQL password. Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm ...
3
votes
2answers
214 views

How can I mimic functionality of SQL Server `TYPE` in Oracle?

I apologize if question title is vague, but I was not able to formulate it better. What I'd like to have is an alternative to TSQL CREATE TYPE, so I can use it in column definition. For instance, I ...
4
votes
3answers
31k views

What's the difference between DBA and DA?

In categorization of users I have seen two different categorizations: First one: End user DBA DBP and Second one: End user DBA DA What's the difference between DA and DBA ?
5
votes
2answers
6k views

Does mysql SUPER privilege include ALL?

I'm granting the following rights to a user: grant super on . to 'user'@'host'; does this include the rights on dbname given by: grant all on dbname.* to 'user'@'host'; thank you
14
votes
1answer
13k views

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, ...